6

SQL Server shows huge decrease in performance when query is written like:

select top 101 name
from Dogs
order by name

compared 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?

Improve this question
7
  • 1
    I guess you are referring to how the "Top N" operator does its sorting. Have a look at Sorting, Row Goals, and the TOP 100 Problem. One fix would be to remove the need of a sort by adding an index on name.
    – Mikael Eriksson
    May 27, 2014 at 8:58
  • Sorry, this isn't actually my query (I wrote simple query to reflect the problem more clearly). The values I am ordering by are derived from mathematical operations, so column can't be indexed. The action, which bothers me the most is this: i want top 1000 records, which contains 1 float and 1 int, but SQL SERVER starts sorting them all (I guess), spills to tempdb and query takes long. Maybe there are choices to construct query in different way to get the same results?
    – user2820173
    May 27, 2014 at 9:53
  • > he values I am ordering by are derived from mathematical operations, so column can't be indexed. You can use computed column in the table and build index on it.
    – msi77
    May 27, 2014 at 10:12
  • You need to modify your question so it is about what you actually want to know. We can't suggest alternatives to a query that you have not shown.
    – Mikael Eriksson
    May 27, 2014 at 10:26
  • Let's say I have table (IndexId int, x float) and function f(x, y). I arbitrally choose y and I want to retrieve IndexId such that f(x, y) are the smallest.
    – user2820173
    May 27, 2014 at 11:20

1 Answer 1

Reset to default
11

"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.

Improve this answer
5
  • 9
    Here is a blog post that claims there really is a hard-coded value in the The ‘Top N’ Sort operator. "SQL Server always uses the alternative algorithm where the TOP 100 (or fewer) rows are requested."
    – Mikael Eriksson
    May 27, 2014 at 12:56
  • 2
    That is so awesome! I hadn't seen that before - I'm going to edit my post around that, because that's a killer find.
    – Brent Ozar
    May 27, 2014 at 18:28
  • @BrentOzar Anything that Paul White and you guys write is always awesome.
    – Kin Shah
    May 27, 2014 at 18:43
  • 2
    @PaulWhite I mostly just read your stuff for the pictures.
    – Brent Ozar
    Jun 2, 2014 at 13:18
  • I LOVE you people! I ran across this today. A colleague asked me why Top 101 (or higher) was significantly slower that TOP 100 and, at fist I thought he was high. When I checked, I thought I was high. LOL
    – Will Davis
    Apr 5, 2018 at 20:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.