patternsqlModerate
SQL Server "TOP 101" much slower than "TOP 100"
Viewed 0 times
muchtopsql101thanslower100server
Problem
SQL Server shows huge decrease in performance when query is written like:
compared to select top 100.
As far as I know, this happens for the reason that SQL Server in a
Are there any workarounds for this?
select top 101 name
from Dogs
order by namecompared to select top 100.
As far as I know, this happens for the reason that SQL Server in a
top 100+ query simply orders all dataset and picks top records (while top 100 and less queries use more sophisticated algorithm).Are there any workarounds for this?
Solution
"As far as I know, this happens for the reason that SQL Server in a top
100+ query simply orders all dataset and picks top records (while top
100 and less queries use more sophisticated algorithm)."
Your two queries likely have different execution plans. There's some hard-coding in the engine that says the top 100 will perform differently, but you might have stumbled into a problem like a spill into TempDB.
To find out for sure and get custom advice based on the queries you're facing, you can post the execution plans for download. You can use SQL Sentry Plan Explorer to anonymize them if necessary. Post the actual plans, not the estimated plans.
100+ query simply orders all dataset and picks top records (while top
100 and less queries use more sophisticated algorithm)."
Your two queries likely have different execution plans. There's some hard-coding in the engine that says the top 100 will perform differently, but you might have stumbled into a problem like a spill into TempDB.
To find out for sure and get custom advice based on the queries you're facing, you can post the execution plans for download. You can use SQL Sentry Plan Explorer to anonymize them if necessary. Post the actual plans, not the estimated plans.
Context
StackExchange Database Administrators Q#65946, answer score: 11
Revisions (0)
No revisions yet.