May 6, 2011 at 3:53 am
I have a stored procedure that hangs intermittently. I have not been able to identify any obvious blocking. However it is doing an insert and an update into the same table within a transaction :
BEGIN TRANSACTION
INSERT INTO MyTable ....
UPDATE MyTable SET .... FROM (SELECT FROM MyTable ...) x WHERE x.id = MyTable.id
COMMIT TRANSACTION
The data comes from a complex query so, before I try to anaylse it any further, should I be suspicious of this construct?
May 6, 2011 at 6:29 am
It's possible that there is a deadlocking issue going on. How often does the proc get called? Is it possible that the update is running into locked records from a different call to the same proc?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 6, 2011 at 2:17 pm
There's only the one instance running.
SELECT * FROM MyTable with an isolation level of READ UNCOMMITTED showed that the insert had worked while the update was hanging. Taking the update outside of the transaction seems to have fixed it but it would be nice to have a theoretical justification for why it has !
May 8, 2011 at 12:48 pm
My guess is that you should not join "MyTable" several times in your UPDATE statement.
Instead of:
UPDATE MyTable SET .... FROM (SELECT FROM MyTable ...) x WHERE x.id = MyTable.id
Use:
UPDATE t
SET t.... = ...
FROM MyTable t
JOIN ...
There is no need to use nested subquery that refers to the same table you are updating.
UPDATE is very "expensive" command, so use it on as few rows as possible.
How to decrease number of updated rows:
Instead of UPDATE of the same rows you just inserted,
insert them with the values they should finally have and omit update.
If you have to update other rows (rows that were already there before your transaction),
then update those in separate update command.
UPDATE should have strict where clause that removes all rows that already have the values you intend to update to.
If you want to isolate rows by ID prior to update, you can store the id's in the table variable or temp table, and in separate UPDATE join with that.
May 8, 2011 at 10:48 pm
run sp_whoisactive (search sqlblog.com for latest version of this AWESOME freebie) to check for blocking.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply