SQL 2000 Update Query Issue -- Performance

• MikeMahony

  SSC Eights!

  Points: 891

  More actions

  July 18, 2003 at 11:06 am

  #82288

  I am having a strange performance issue on my SQL 2000 server that I wanted to run by all of you and get your opinion.

  I had a program written by another developer that was failing on updates when a large number of records were in a certain table. My new developer decided that the code needed to be rewritten. I had wanted to do that anyway, so I approved that project. That is when the real problem was revealed.

  Here are the steps taken by the program:

  Timer triggers the launch of a function.

  Function updates all records in the queue table as follows:

  Update Table Set Field1 = <value>, Field2 = <value2> Where Field1 = null and field2 = null and Status = 1

  Function then grabs all the records it just updated like so:

  Select ID, FieldA, FieldB, FieldC From Table where Field1 = <value> and Field2 = <value2>

  Function then gets a datareader from this query (I'm using C#).

  Function goes through first record in datareader and determines if certain conditions are true. If all is correct, function attempts to do an update on a single row in the SAME table it got the records from using the following statement:

  Update Table Set Field1 = 0, Status = 2 Where ID = <id value>

  Here is the kicker. If I grab just the top 206 records this all works flawlessly. If I grab the top 207 or higher records the update statement for that single row always fails due to a server timeout.

  I've tried to debug as follows:

  Grab 100 records only.

  Pause program before the update.

  Do an update of that same row in Query Analyzer and it works.

  Grab 207 records.

  pause the program before the update.

  Attempt the update of that one row in query analyzer and it times out.

  What I know now:

  (1) The threshold is 206 records.

  (2) Above 206 both Query analyzer and the ADO.NET command object time out when trying to do the update.

  (3) Below 207 both Query analyzer and the ADO.NET command object work properly when doing the update.

  What could be the cause of this?

• Fubak

  Ten Centuries

  Points: 1114

  More actions

  July 18, 2003 at 11:43 am

  #466099

  I've had problems with updating more than one variable in an UPDATE statement. Split them into 2 lines and see what happens. Also, post the table definition to help us out.

• Goce Smilevski

  SSCommitted

  Points: 1704

  More actions

  July 21, 2003 at 1:13 am

  #466151

  quote:

  ---

  Function updates all records in the queue table as follows:

  Update Table Set Field1 = <value>, Field2 = <value2> Where Field1 = null and field2 = null and Status = 1

  Function then grabs all the records it just updated like so:

  Select ID, FieldA, FieldB, FieldC From Table where Field1 = <value> and Field2 = <value2>

  .....

  Update Table Set Field1 = 0, Status = 2 Where ID = <id value>

  ---

  The function updated only those rows with Field1 = null and field2 = null and Status = 1. Later, it grabs records with NO condition on the status. Is it possible that there is a row in the table that the function grabs and later tries to update ? What I am trying to say is that this may not be a performance problem at all. It may be a locking problem.

• eholsinger

  Mr or Mrs. 500

  Points: 525

  More actions

  July 21, 2003 at 5:47 am

  #466179

  sounds like a locking problem - do the steps synchronously and insure that each step is complete before proceeding to the next step - haven't played w/c# yet, but you should be able to do this w/ado

• gregj

  Old Hand

  Points: 346

  More actions

  July 21, 2003 at 7:34 am

  #466202

  First (but likely not the cause of any of your problems), I would suggest using ISNULL() instead of <fieldname> = NULL.

  Secondly, I would suggest using SQL Profiler to determine if it a performance problem or locking problem.