How much do you know about SQL select Top N?

The SQL select Top N statement is a very important statement and is indispensable in implementing paging queries. because paging queries usually involve tables with a large number of records and are shared by a large number of users, optimization is meaningful.

There are several variants to implement SQL Top N:

1. Set rowcount @ n; Select... order by somefields

2. Select top (@ n)... order by somefields

3. Select top (XX)... order by somefields

-- XX is a constant, for example, 10.

In the somefields referenced in the preceding query, if the involved table has an index on it, it is a situation where no index is available.
If there is an index, even if the table contains many records, it will not cause too much performance problems.
If there is no index, there will also be actual needs, such as real-time identification of the top 100 products that sell the best. When no index is available, Top N is searched. If careful optimization is not performed, the performance will be greatly affected, and the entire system may even be paralyzed.

To optimize Top N, it is necessary to understand how SQL server handles the preceding Top N deformation. the following article was posted on the MS forum. I am too lazy to translate it into Chinese. Share it with you.

 

Original article)

 

Question:

-- Fast
1. Select Top 100 * from test where c1 <30000 order by C2

-- Slow
2. Select Top 101 * from test where c1 <30000 order by C2

 

1. is more than two times faster than 2.

 

Why?

 

What a coinccident! I am on the same issue just at the time.
I was considering implementing an algorithm like this:
First populate the N rows to a table variable (with index on the sort column), then iterate through all left rows, adding one row to the table variable if bigger than min of the table, else discard it. this cocould be either done in SQL or CLR aggregate function.
Then I thought maybe MS had already done it in the top N stuff, so started to run a test against it.

Create Table [DBO]. [num]
([N] int not null, s varchar (128) null, primary key clustered ([N] ASC ))
Go
-- Populate data
Set nocount on
Declare @ n int, @ I int
Set @ n = 1000000
Set @ I = 0
While @ n> 0 begin
If @ I = 0 begin tran
Insert into DBO. Num
Select @ n, convert (varchar, @ n + @ I * 2)
Set @ n = @ n-1
Set @ I = (@ I + 1) % 1000
If @ I = 0 commit
End
Go
-- Test 1
Select top (XX) cast (S as INT), N from DBO. Num
Order by cast (S as I

NT) DESC
Go
-- Test 2
Set rowcount xx
Select cast (S as INT), N from DBO. Num
Order by cast (S as INT) DESC
For test 1, duration <1 s, for any XX <= 100, and the duration is about 12 s for any XX> 100

For test 2, the duration is fixed at 4S for XX: 10-100,000.

The show-plan shows Test 1 uses Top N sort op, while the test 2 uses sort op.
OK I dont care about the sort op. The only thing I care is if the MS has correctly implemented the ton n sort.
Msdn stated about "Top N sort ":
"Top N sortIs similar toSortIterator, doesn t that only the firstNRows are needed, and not the entire result set. For small valuesN, The SQL Server query execution engine attempts to perform the entire sort operation in memory. For large valuesN, The query execution engine resorts to the more generic method of sorting to whichNIs not a parameter ."

As you can see, this statement sound like the algorithm I was intending to write myself. But the later part mentioned a "more generic method of sorting to whichNIs not a parameter ", that exlains why no matter how XX changes for test1 after going beyong 100, the duration is always the same. Test 2 is also insensitive to n.
So MS seems used 3 algorithm, in which two of them are used for "Top N", one is for "set rowcount ".

I do not think whether to perform it in memory or not will cause such a big difference. it's mainly due to that only one (the fastest one) uses the algorithm of just keeping the Top N rows and then evict low ranking items when they fall below the N window.

I am using a SQL 2005.

I also tested the "select top (@ n)" variation. The result shows that "select top (@ n)" is similar to "set rowcount ...".
The reason I tested the "select top (@ n)" variation is that I was wondering if we cocould use plan-force to force it use the faster "Top N sort ". however it seems that "select top (@ n)" is quite different from "select top (XX)" Where XX is a constant, but similar to "set rowcount ;... ". guess it will not work, so I will not try to test if Plan-force can do the job.

Just curious why MS choose not to use the "Top N sort" algorithm always, instead to choose this so complex arrangement (I. e. some with "Top N sort", some with the "Sort then top "). I think, "Top N sort" shoshould always be used