sql分頁效能測試結果

標籤：des   style   blog   color   os   資料   re   c   

--方案一：

declare @d datetimeset @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 datetimeset @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 IDselect [Max方法升序分頁執行花費時間(毫秒)]=datediff(ms,@s,getdate()) --13ms

--方案三：

declare @q datetimeset @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 descselect [Min方法降序分頁執行花費時間(毫秒)]=datediff(ms,@q,getdate())--10ms

 

--方案四：

declare @r datetimeset @r = getdate()select top 10 * from ( select row_number()over( order by id desc) as rowNumber,* from info) Twhere 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;

方案五沒有採取top的形式取資料，而是用了between，相同點都用了row_number()函數。根據平常的分頁習慣，方案四、五效率最高,且效能相當.