SQL Server Performance Forum – Threads Archive
Best way to increment a counter in a field.Hi, I usually do UPDATE blah SET blah = blah + 100 WHERE blahid = 5. I’m noticing though, by looking at traces, that this is causing a lot of reads (100-150). SELECT * WHERE blahid = 5 only causes 0-10 reads. Note: there are no indexes on blah, so SQL Server should not have to update any indexes. Any ideas?
Just a gess, without more information.
If there is no index, then SQL has to look all rows checking blah = 5.
When you use select, sql use a phantom index or a statistics to resolve where clause.
oh, Sorry. Naturally there is an index on blahid (it’s the clustered index). I meant there is no index on blah (the field to update). Can’t figure out why the number of reads is 10x larger than the SELECT. Execution plan is just a clustered index seek (on blahid) followed by the update!
What SQL do you have?
The use of blah was a bad idea <img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /><br /><br />Let me try again:<br /><br />UPDATE table SET datafield = datafield + 100 WHERE indexfield = 123456789<br /><br />Trace shows this to cause 100+ reads.<br /><br />SELECT * FROM table WHERE indexfield = 123456789<br /><br />Trace shows this to be < 10 reads.<br /><br />No index on datafield. Clustered on indexfield.<br />
2000 Service Pack 3 Strangely enough, when run through QA it says only 2-3 logical reads. I know the number of reads in the Profiler Traces and QA don’t match up, but that’s quite a large difference. Hmmm, I think there might be something screwy with the traces I’m running. In any case, is this the typical/best way of incrementing a counter?
I don’t see a better way, but I’m not a developer so I suggest to wait for expert members post.
Is this update in a procedure? If so, look at your recompiles. They are not caught in QA with SET STATISTICS IO ON. They will be reflected in QA though. Just an idea. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Just a wild guess would be that the table and the index are heavily fragmented ehich is causing these reads. Gaurav
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.