Beware! SELECT TOP is BUGGED!
"Yes it is true. And this bug has just been discovered by me and confirmed by the Query Optimizer Project Lead (Conor Cunningham) himself, that i have the chance to meet at SQL PASS 2005.
He has been very kind and after I exposed the problem to him, after a little bit of suprise :-), in few days he confirmed that in some circustances SET ROWCOUNT, which is deprecated, is faster than the usually better SELECT TOP.
You can test it yourself, downloading the script right here. I must say that the problem only shows when you need to limit the rows returned by your query while you're sorting over a colum with no index. At least this is my experience. Fortnately this is really an uncommon scenario so the bug is not really a tremendous one, but ouy should better know it since using SET ROWCOUNT under this cirumnstances can speed up your query by more that 3/4 times!
I've been also confirmed that this bug will not be fixed in the forthcoming version of SQL Server 2005 but will be fixed in the first service pack. ..."
An interesting bug.
Given the reported speed impact, this is something I need to look into when we move to SQL 2k5... (Which will be agressive as there are some features in SQL 2k5 that I can use ASAP).
No comments:
Post a Comment
NOTE: Anonymous Commenting has been turned off for a while... The comment spammers are just killing me...
ALL comments are moderated. I will review every comment before it will appear on the blog.
Your comment WILL NOT APPEAR UNTIL I approve it. This may take some hours...
I reserve, and will use, the right to not approve ANY comment for ANY reason. I will not usually, but if it's off topic, spam (or even close to spam-like), inflammatory, mean, etc, etc, well... then...
Please see my comment policy for more information if you are interested.
Thanks,
Greg
PS. I am proactively moderating comments. Your comment WILL NOT APPEAR UNTIL I approve it. This may take some hours...