Solved

Updating large number of records on SQL.  Very very slow.

Posted on 2004-04-14
9
349 Views
Last Modified: 2008-03-17
Hi,

I'm trying to download Event Log entries and put them in a SQL database.  Using the Count(*) and refreshing, I estimate that it updates about 5 per second up to about 1800, and after that it becomes about 1 record every 5 seconds.  There are a total of 29000 records.  I am using WMI to get the event logs.

I stopped my browser.  It continued at the 1 per 5 seconds or more pace.  I stopped IIS, and it still continued to update.  It finally stopped when I restarted the SQL Server service.  So it looks like SQL server is the bottleneck.  CPU free % is about 99%.  

Why is SQL server so slow?  Thanks for the help.  BTW, code is listed below:

Set colRetrievedEvents = objWMIService.ExecQuery(strSearchString)
For Each objEvent in colRetrievedEvents
      
      objRS.AddNew
      If Trim(strServer) = "" Then
            strServerTemp = strLocalComputer
      Else
            strServerTemp = strServer
      End If
      objRS("Server") = strServerTemp
      objRS("DateDumped") = Now()
      If Trim(Session("UserDomain")) = "" Then
            strUserTemp = Session("UserName")
      Else
      strUserTemp = Session("UserDomain") & "\" & Session("UserName")
      End If
      objRS("UserDumped") = strUserTemp
      If Trim(strDomain) = "" Then
            strUserTemp = strUser
      Else
            strUserTemp = strDomain & "\" & strUser
      End If
      objRS("AccountUsed") = strUserTemp
      objRS("Category") = objEvent.Category
      objRS("CategoryString") = objEvent.CategoryString
      objRS("ComputerName") = objEvent.ComputerName
      objRS("EventCode") = objEvent.EventCode
      objRS("EventIdentifier") = objEvent.EventIdentifier
      objRS("EventType") = objEvent.EventType
      objRS("LogFile") = objEvent.Logfile
      objRS("Message") = objEvent.Message
      objRS("RecordNumber") = objEvent.RecordNumber
      objRS("SourceName") = objEvent.SourceName
      objRS("Type") = objEvent.Type
                objRS("UserAccount") = objEvent.User
      objRS.Update
Next
0
Comment
Question by:ojfahoum
9 Comments
 
LVL 3

Expert Comment

by:diegojserrano
Comment Utility
first, build an insert statement and execute with ado connection execute method instead of using ado recordset update method.

if it is still very slow, comment all lines in the for loop, leave only the for statement to loop inside the collection, to see how much time is needed to see if the delay is due to sql server or wmi
0
 

Author Comment

by:ojfahoum
Comment Utility
You were right on.  It's the WMI.  Commenting out everything expect a
Response.Write i & " "

it starts slowing down significantly at various points.  Sometimes at 1700, sometimes at 600, sometimes at 150.

Anybody ever see something like this, or have any ideas.

Any info would be greatly appreciated. Thanks.

0
 
LVL 19

Expert Comment

by:Melih SARICA
Comment Utility
Are u deleting log entry that u add to ur sql server?
Working with the same number of log entries ?



0
 
LVL 1

Expert Comment

by:FreddieD
Comment Utility
Why don`t you use Stored Procedures ?

I update up to 35000 record in a few seconds.The longest time so far was about 10 seconds.

Which programming Language are you using ?

Create a new database project.
Add a new item
Make the item a stored procedure.

Call it whatever you like.

Paste this inside.(Without the arrows and dashes("<--->"))

<----------------------------------------------------------------------------------------------------------------------------------------------------------->

USE [DevFred]  (Specify the Database name in the square brackets)

GO

SET NOCOUNT ON

GO

-- Drop the  procedure if it already exists.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Update_Table]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Update_Table]

GO

CREATE PROCEDURE [dbo].[Update_Table]
     
     @AccountUsed nvarchar(20),
     @Category nvarchar(20),
     @CategoryString nvarchar(20),
     @ComputerName nvarchar(15),
     @EventCode nvarchar(10),
     @EventIdentifier int,
     @EventType  nvarchar(5),
     @LogFile nvarchar(20),
     @Message nvarchar(50),
     @RecordNumber int,
     @SourceName nvarchar(20),
     @Type nvarchar(10),
     @UserAccount nvarchar(20)

AS
SET NOCOUNT ON
-- INSERT a new row in the table.
INSERT [dbo].[Company]
(
   
     [AccountUsed],
     [Category],
     [CategoryString],
     [ComputerName],
     [EventCode],
     [EventIdentifier],
     [EventType],
     [LogFile],
     [Message],
     [RecordNumber],
     [SourceName],
     [Type nvarchar],
     [UserAccount]

)

VALUES

(

     @AccountUsed ,
     @Category ,
     @CategoryString ,
     @ComputerName,
     @EventCode,
     @EventIdentifier,
     @EventType ,
     @LogFile ,
     @Message ,
     @RecordNumber,
     @SourceName,
     @Type ,
     @UserAccount

GO

<---------------------------------------------------------------------------------------------------------------------------------------------------------->


Run it on your database by right clicking and then clicking on run.

In you vb app paste the following code.

<-------------------------------------------------------------------------------------------------------------------------------------------->

 Dim strDbConnection As String = ("Data Source=METERSSRV;Initial catalog=DEVFRED;Integrated Security=SSPI")
 Dim cmd As SqlCommand = New SqlCommand

(You can put this in a click event of a button or whatever you like)

            Dim cn As New SqlConnection

            cn = New SqlConnection(strDbConnection)
            cn.Open()

            cmd.Connection = cn
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "Update_Table"

            cmd.Parameters.Add(New SqlParameter("@AccountUsed ", strAccountUsed))
            cmd.Parameters.Add(New SqlParameter("@Category", strCategory))
            cmd.Parameters.Add(New SqlParameter("@CategoryString", strCategoryString))
            cmd.Parameters.Add(New SqlParameter("@ComputerName", strComputerName))
            cmd.Parameters.Add(New SqlParameter("@EventCode", strEventCode))
            cmd.Parameters.Add(New SqlParameter("@EventIdentifier", strEventIdentifier))
            cmd.Parameters.Add(New SqlParameter("@EventType", strEventType))
            cmd.Parameters.Add(New SqlParameter("@LogFile", strLogFile))
            cmd.Parameters.Add(New SqlParameter("@Message ", strMessage))
            cmd.Parameters.Add(New SqlParameter("@recordNumber", strRecordNumber))
            cmd.Parameters.Add(New SqlParameter("@SourceName", SourceName))
            cmd.Parameters.Add(New SqlParameter("@Type", Type))
            cmd.Parameters.Add(New SqlParameter("@UserAccount",strUserAccount))

            cmd.ExecuteNonQuery()


This should work but if it doesn`t please let me know,and maybe you can give me more information on what youre doing,and give me your`e database structure and all your field details,like field type,lentgh and so on.

Then I could create a similar type app using the same fields and database and then I could give you all the source code.

Good luck.


0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:ojfahoum
Comment Utility
Thanks for all the info.  I tried just a real basic stored procedure with just one variable.  It basically did the same thing.  So I don't think the problem is with SQL Server, it seems to be a WMI issue.
0
 
LVL 1

Expert Comment

by:FreddieD
Comment Utility
Have you tried running the WMI or the stored procedure on a THREAD ?
0
 

Author Comment

by:ojfahoum
Comment Utility
I'm writing a compiled version of the script to see if that runs any faster.  Will update on results.

I actually don't know how to run and/or create a thread.  At least not yet.
0
 
LVL 1

Accepted Solution

by:
FreddieD earned 500 total points
Comment Utility
Dim tUpdate as new thread

tUpdate = new thread(addressof Update())
tUpdate.start()


Put all you update code in a Sub procedure called  " Update() ".

0
 

Author Comment

by:ojfahoum
Comment Utility
I ran a compiled version of my code, and it still is slow.  It seems the problem is WMI Log Dump.  It looks like I will have to find another way to dump the log files.  Thanks for all the help Freddie.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

RIA (Rich Internet Application) tools are interactive internet applications which have many of the characteristics of desktop applications. The RIA tools typically deliver output either by the way of a site-specific browser or via browser plug-in. T…
This article will show, step by step, how to integrate R code into a R Sweave document
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now