September 11, 2013 at 11:46 am
Hello Everyone
I am a bit curious after doing some accidental testing. I was going thru someone elses code, and noticed that on a few Select queries, they were using SELECT TOP 50000 etc, etc....
But there are only something like 60 rows in that table. I removed the TOP clause and the query ran much faster and with a lot less CPU cycles. Is there truly something less efficient about using the TOP clause, with some really high number, when there are not nearly that many rows in the table? That is almost like using SELECT TOP 100% without a WHERE clause.
Does removing the TOP clause and the large number, really increase the performance when there are not nearly that many rows in that particular table?
Thanks in advance for any and all suggestions, comments and advice
Andrew SQLDBA
September 11, 2013 at 12:30 pm
Probably looking at the actual exection plan will provide you with the answer.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 11, 2013 at 12:54 pm
Interesting question. I did a couple of simple tests where I did a TOP, without an ORDER BY since you didn't say there was one. The estimated costs were roughly the same. The execution times and I/O were pretty much identical. But, the simple query that I used resulted in a scan on both queries and had the additional TOP operator in the one that included TOP. Same thing on a more complicated query, I wouldn't be at all surprised for the additional operator to cause additional overhead.
While you know that you don't need to use the TOP to resolve the query, SQL Server will still try.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 11, 2013 at 12:57 pm
Depends on the query, but TOP adds a row goal to the query which could result in the row estimations being wrong (the row goal gets used instead of the estimation), which can cause all sorts of fun, depending whether the estimate is high or low.
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
September 11, 2013 at 11:25 pm
AS Koen said Please provide the actual execution plan then only we can provide you some suggestion...
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 13, 2013 at 7:35 am
These queries weren't in views, by any chance? Using SELECT TOP xxxxx (where xxxxx is a number far larger than the maximum possible rows ever expected to be returned by the view) was a gross hack to force SQL 2000 to return the results from the view in an ordered fashion; it also worked in SQL 2005, but you had to set a trace flag and have the database in SQL 2000 compatibility for it to work. Doesn't work in any modern version of SQL, though.
September 13, 2013 at 7:38 am
No, I try to never use a View unless absolutely necessary.
Andrew SQLDBA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply