December 17, 2012 at 12:57 am
If there is an update statement using 'where' clause, and the 'where' clause returns ten rows, how exactly sql engine will process this?
1. Will this store the read result(10 rows) in temp table, and update the rows one by one. Or, can issue update statement for more than one rows at a time? Or will issue the update stmt in sequential manner - row 9 update is not completed yet and row 10 update command issued?
December 17, 2012 at 1:01 am
Could you be more specific please?
If you mean something like this:
UPDATE Table SET SomeColumn = SomeValue WHERE SomeID between 1 and 10 -- updates 10 rows
then there's no temp table involved there. SQL locates the 10 rows (via seek or scan as applicable) and updates each one.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 17, 2012 at 1:37 am
Thanks Gila for making it specific. Yes, that is the required scenario. But my concern remain unanswered is - how exactly the update process will occur internally? All in one shot? Or in a sequntial manner -- If yes, which row first?
December 17, 2012 at 1:51 am
ganeshkumar005 (12/17/2012)
But my concern remain unanswered is - how exactly the update process will occur internally? All in one shot? Or in a sequntial manner -- If yes, which row first?
Why does it matter?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 17, 2012 at 2:36 am
I am facing a deadlock issue. I can see application is sending update command on db and before that SPID is completed, it is sending the same command again, may be for a different row, because page ID is different in deadlock graph.
December 17, 2012 at 2:46 am
Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.
DBCC TRACEON(1222,-1)
How the query processor internally executes an update will have no bearing on your deadlock. All required rows are locked exclusive before the update starts and those locks are only released after the transaction commits
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply