Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2004-04-14
9
Medium Priority
?
359 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 2000 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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
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 …
Progress
Introduction to Processes

571 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