Solved

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

Posted on 2004-04-14
9
350 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
ID: 10828795
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
ID: 10829246
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
ID: 10830660
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
ID: 10833057
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:ojfahoum
ID: 10838354
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
ID: 10839646
Have you tried running the WMI or the stored procedure on a THREAD ?
0
 

Author Comment

by:ojfahoum
ID: 10891666
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
ID: 10896471
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
ID: 10905133
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is about my first experience with programming Arduino.
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
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 …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

920 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

16 Experts available now in Live!

Get 1:1 Help Now