——难得贴个技术贴。。。。。。

分页利器，sql2005的row_number函数效率 [原创 2008-09-17 12:22:19]

测试了一下，100万条记录的表，3个sql的开销
不加索引，各33%
加了索引，多次top倒腾占78%，后2个都是11%
那么，说明sql2005的row_number函数效率还是可以的：比多次top倒腾快6倍！

以前db2 v6的时候就使用过rank over，感觉不错，就怕效率会比普通的sql会低很多。这次的测试，看来至少sql2k5的row_number的效率不算低

declare @topn int,@skipm int
set @topn=100
set @skipm=3000

select *
from (
........select top (@topn) *
........from (
................select top (@skipm+@topn)
................*
................from serial
................order by pn desc,sn asc
........) a
........order by pn asc,sn desc
) a
order by pn desc,sn asc

select * from (
........select
........ROW_NUMBER ( ) OVER ( order by pn desc,sn asc ) as fno
........,*
........from serial
) a
where fno>@skipm and fno<=@skipm+@topn
order by fno

select * from (
........select
........ROW_NUMBER ( ) OVER ( order by pn desc,sn asc ) as fno
........,*
........from (
................select top (@skipm+@topn)
................*
................from serial
................order by pn desc,sn asc
........) a
) a
where fno>@skipm and fno<=@skipm+@topn
order by fno