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
stressfreewebsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
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?
0
stressfreewebsAuthor Commented:
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
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

stressfreewebsAuthor Commented:
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
0
Kyle AbrahamsSenior .Net DeveloperCommented:
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.
0
stressfreewebsAuthor Commented:
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
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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...
0
stressfreewebsAuthor Commented:
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
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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...
0
stressfreewebsAuthor Commented:
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
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
are you also reading in the data in the same page?
0
stressfreewebsAuthor Commented:
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
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
it's not the best solution, but it's the indication that the SELECT is doing a LOCK on the data indeed.

you have a couple of alternatives...
I suggest this one:
http://msdn.microsoft.com/en-us/library/office/aa140098%28v=office.10%29.aspx

replace: Set recEML = recEML_cmd.Execute
by
Set recEML = Server.CreateObject ("ADODB.Recordset")
recEML.CursorType = 0 'adOpenForwardOnly
recEML.CursorLocation = 3 'adUseClient
recEML.Open Command1

this should work better...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stressfreewebsAuthor Commented:
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
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
recEML.Open

must be:

recEML.Open recEML_cmd
0
stressfreewebsAuthor Commented:
Worked a charm - thank for your help Angel!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.