Solved

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

Posted on 2004-04-14
9
353 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
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…

726 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