Avatar of stressfreewebs
stressfreewebs asked on

How to fix a slow running update statement

Hi Experts,

I have an e-mail table which has reached around 90,000 records. This is running on MSSQL server, and has a size of 1.5GB.

A script is hanging, and after a lot of response.write / response.flush statements through the ASP document, I have isolated the exact line which it is timing out on.

UPDATE EMails SET eProcessedDate = '2013-09-09 14:58:53', eProcessedBy = 0, eAutoIndex = 1, eStatus = 2 , eContract = 122 WHERE eId = 89611

eProcessedDate is a datetime field
All the others are integers

I can't work out why such a simple statement would hang.

Can anyone advise steps to optimise the table, or to diagnose why it is hanging?

Thanks!
Dan
Microsoft SQL ServerASPWindows Server 2008

Avatar of undefined
Last Comment
stressfreewebs

8/22/2022 - Mon
Guy Hengel [angelIII / a3]

is there a index on the field eId ?
if not: create one
if yes: show the explain plan of the query, please.
is there a trigger on the table? the issue might be there.
Kyle Abrahams

Also are there any locks or are you doing anything in a transaction which would prevent the record from being updated right away?

If you run that in SSMS outside of your application, does it still take as long?

How many records in the table?
ASKER
stressfreewebs

Hi Angel,

The field eId is the primary key of the table, which I believe makes it an index automatically? Here is the execution plan (or what I was able to capture). The operator cost at the second step was 0.0132842 which sounds quite ok to me?

There are no triggers on the table.

Hope I attached the right data?! Wasn't sure what to get.

Thanks,
Dan
exeplan.PNG
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
stressfreewebs

Hi Ged,

There are 90,000 records in the table. I don't think there are any locks but will Google to find out how to check this and get back to you.

Thanks,
Dan
Kyle Abrahams

Primary key is the fastest way to access the record and you are correct, has a clustered index on it.  

Just for giggles can you run it in SSMS (Management Studio)?  I wonder if there's something else going on in the framework rather than the update statement itself.
ASKER
stressfreewebs

Hi Ged,

I ran the command "select cmd,* from sys.sysprocesses
where blocked > 0"

Then started the effected script running, and just after it had locked up re-ran the above command in the SQL server interface and sure enough, there is an update lock on the given table which appears at the same time as my response.flush() commands show the script is stuck - so does look like it's a lock issue.

Not sure how to solve this though! Any ideas?

The SQL statement is:

Set Command1 = CreateObject ("ADODB.Command")
Command1.ActiveConnection = MM_connMb_STRING
Command1.CommandText = "UPDATE EMails SET eProcessedDate = '"& sqldate(DATE()) &" "& TIME() &"', eProcessedBy = 0, eAutoIndex = 1, eStatus = 2 "& upd &" WHERE eId = "& recEML.Fields.Item("eId").Value &""
Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = true

Open in new window


The e-mails table is already opened via a Select command thus (but this doesn't seem to lock it):

Set recEML_cmd = Server.CreateObject ("ADODB.Command")
recEML_cmd.ActiveConnection = MM_connMb_STRING
recEML_cmd.CommandText = "SELECT TOP 50 * FROM dbo.EMails WHERE eStatus = 0 AND eAutoIndex <> -1" 
recEML_cmd.Prepared = true

Set recEML = recEML_cmd.Execute
recEML_numRows = 0

Open in new window


Thanks for your help,
Dan
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Guy Hengel [angelIII / a3]

You must have a uncommitted transaction open on that table.
Did you test the UPDATE in sql server mangagement studio?
Then a simple COMMIT will unlock...
ASKER
stressfreewebs

Hi Angel,

I did test the update in the SQL server management studio - it worked fine. Then I ran the web script which was causing the problem and allowed it to hang before running the query in SSMS again - whereupon it failed because of the lock.

I have amended the update code thus:

Set Command1 = CreateObject ("ADODB.Command")
Command1.ActiveConnection = MM_connMb_STRING
Command1.CommandText = "UPDATE EMails SET eProcessedDate = '"& sqldate(DATE()) &" "& TIME() &"', eProcessedBy = 0, eAutoIndex = 1, eStatus = 2 "& upd &" WHERE eId = "& recEML.Fields.Item("eId").Value &""
Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = true
response.write("Ready...")
response.flush()
Command1.Execute()
response.write("...Done!")
response.flush()

Open in new window


Just to test if it is failing on the execution line, and it prints "Ready..." to the screen and then sometime later the timeout message.

How can I find how the lock is getting there and stop it happening?

Thank you,

Dan
Guy Hengel [angelIII / a3]

>I did test the update in the SQL server management studio - it worked fine.
it makes me more and more sure you didn't commit ion the SSMS ...
you can check in your sql server, the active sessions ...
the web server's session should be there and be locked, and you see the locking session id also...
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
stressfreewebs

Hi Angel,

I'm finding my way around so many more things in MSSQL server today - thank you!

In the activity monitor I can see a stack of suspended tasks - I presume this is a lock - some have wait type CXPACKET and others say LCK_M_IX or LCK_M_S - but these are all from the IIS login in the sites name, not from the server itself.

Does that help at all, or is there any information I can get you from the activity monitor which would help?

Dan
Guy Hengel [angelIII / a3]

are you also reading in the data in the same page?
ASKER
stressfreewebs

I found a solution but I am unsure as to any potential issues it may cause, or why it even happened.

From the Activity Monitor I can see that the select statement, which is loading up the details of the records to be updated, it locking the update statement from executing, which is what is causing the script to time out.

So basic summary of the issue by simplifying the code to remove the bits that aren't involved:

Set recEML_cmd = Server.CreateObject ("ADODB.Command")
recEML_cmd.ActiveConnection = MM_connMb_STRING
recEML_cmd.CommandText = "SELECT TOP 50 * FROM dbo.EMails WHERE eStatus = 0 AND eAutoIndex <> -1" 
recEML_cmd.Prepared = true

Set recEML = recEML_cmd.Execute
recEML_numRows = 0

DO While NOT recEML.EOF

Set Command1 = CreateObject ("ADODB.Command")
Command1.ActiveConnection = MM_connMb_STRING
Command1.CommandText = "UPDATE EMails SET eProcessedDate = '"& sqldate(DATE()) &" "& TIME() &"', eProcessedBy = 0, eAutoIndex = 1, eStatus = 2 "& upd &" WHERE eId = "& recEML.Fields.Item("eId").Value &""
Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = true

recEML.MoveNext()
LOOP

Open in new window


By adding "WITH (NOLOCK)" to the select statement, the script runs superquick.

However - I've never had to use this before in this situation, which I must come across once or twice a month at least - i.e. Select to determine which records to update, then loop through an update of those records. Is it a problem that I've only just experienced now due to this table being quite large in size?

Is adding the NOLOCK the best solution? Are there any likely pitfalls?

Thanks,
Dan
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Guy Hengel [angelIII / a3]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
stressfreewebs

Hi Angel,

I'm getting an error on the final line of that change thus:

ADODB.Recordset error '800a0e7d'

The connection cannot be used to perform this operation. It is either closed or invalid in this context.

I've googled that error code but can't claim I understand! The code for the recordset is now:

Set recEML_cmd = Server.CreateObject ("ADODB.Command")
recEML_cmd.ActiveConnection = MM_connMb_STRING
recEML_cmd.CommandText = "SELECT TOP 50 * FROM dbo.EMails WHERE eStatus = 0 AND eAutoIndex <> -1"
recEML_cmd.Prepared = true

Set recEML = Server.CreateObject ("ADODB.Recordset")
recEML.CursorType = 0 'adOpenForwardOnly
recEML.CursorLocation = 3 'adUseClient
recEML.Open
recEML_numRows = 0

Thanks,
Dan
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
stressfreewebs

Worked a charm - thank for your help Angel!