你知道多少關於sql select top N?

sql select top N 語句是一個非常重要的語句, 在實現分頁查詢中是不可或缺的. 由於分頁查詢通常涉及含有大量記錄的表， 並且是為大量使用者分享的任務，因此，對其進行最佳化是很有意義的。

實現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是一個常數， 比如10

在上述的查詢中引用的somefields, 如果涉及的表在其上有索引是一種情況， 沒有索引又是一種情況。
有索引的話，即使表含有很多記錄，也不會對效能造成太大問題。
沒有索引的情況也是會有實際需求的，比如即時的找出銷售最好的前100個產品。在沒有索引時的尋找Top N， 如果不進行細緻的最佳化，會對效能造成很大的影響，甚至會使得整個系統癱瘓。

如果要對top n進行最佳化，那麼瞭解sql server 是如何處理上述的top n 的幾種變形就是很有必要的. 下面的文章是我在MS的論壇上發的， 我自己懶得翻譯成中文了，和大家共用一下吧。

 

原文(是我在http://social.msdn.microsoft.com/Forums/en/transactsql/thread/944ad896-b34c-4dea-af55-cfbae79251f6上的一個回貼)

 

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 could 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 < 1s, for any XX <= 100, and the duration is about 12s 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 MS has correctly implemented the Ton N Sort.
MSDN stated about "Top N sort":  
"Top N Sort is similar to the Sort iterator, except that only the first N rows are needed, and not the entire result set. For small values of N, the SQL Server query execution engine attempts to perform the entire sort operation in memory. For large values of N, the query execution engine resorts to the more generic method of sorting to which N is 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 which N is 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 could 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" should always be used