Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gaurang.vora
Starting Member
2 Posts |
 Posted - 2009-10-26 : 07:18:46
|
| Hello,To brief on is, i have to select top 10 records which are not locked by other user and allocate those 10 records to the current user by updating the owner name, state and step column. I have a table TrnState having columns id (identity), tifname, batchid, state, step & owner name. I have a SP which is called through a .net app from five machines which allocates 10 records to that user, then processes it and again runs the SPs.My current query is as below:Update TrnState Set Step='OCRImageProcess',State='Processing', currentOwner=@DlUser,ownTime= getdate()Where (tifname+convert(nvarchar,batchid)) in (Select Top 10 (TrnState.tifName+convert(nvarchar,batchid)) FROM TrnState WITH (rowlock,updlock) LEFT JOIN Batch ON (TrnState.batchId = Batch.id) WHERE TrnState.step='OCRFindBorder' AND TrnState.state='complete' ORDER BY Batch.priority, Batch.scanComplete, TrnState.tifName)The indexes on this table are as below:Non Clustered Index on batchid, tifname, step and stateClustered Index on ID (Primary Key)Non Clustered on tifname and batchidThe profiles trace is as below:recompiles execution_count total_wait_time cpu_time reads writes10 29560 2034486992 56915497 80257503 701Please help me to optimise the above query |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-10-26 : 11:10:27
|
| try this, break out that crazy statement starting from the WHERE clause onward into a temp table and update trnState from that temp table. index the temp table on the key fields you are joining intotry that out and see if you see some optimization..<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
 |
|
|
gaurang.vora
Starting Member
2 Posts |
Posted - 2009-10-27 : 10:27:10
|
| Thanks yosiasz for reply, but i did not understand what you are trying to say, can you please write into a bit detail. |
|
|
|
|
|
|
|
|
Optimising Update