sql分页性能测试结果

declare @d datetime
set @d = getdate()
select top 10 * from Info where ID not in (select top 9990 ID from Info order by ID) order by ID 
select [not in方法升序分页执行花费时间(毫秒)]=datediff(ms,@d,getdate()) --430ms

declare @s datetime
set @s = getdate()
select top 10 * from info where ID >=(select max(ID) from (select top 9991 ID from info order by ID) as T) order by ID
select [Max方法升序分页执行花费时间(毫秒)]=datediff(ms,@s,getdate()) --13ms

declare @q datetime
set @q = getdate()
select top 10 * from Info where ID <=(select min(ID) from (select top 9991 ID from Info order by ID desc) as T) order by ID desc
select [Min方法降序分页执行花费时间(毫秒)]=datediff(ms,@q,getdate())--10ms

declare @r datetime
set @r = getdate()
select top 10 * from 
( 
select row_number()over( order by id desc) as rowNumber,* from info

) T
where T.rowNumber>0 
select [row_number()方法降序分页执行花费时间(毫秒)]=datediff(ms,@r,getdate())--3ms

with infos as
(
select r.*, row_number()over(order by r.id desc) as r 
from [Info]  r
) 
select * from infos where r between 1 and 30;