http://feiyu.asgard.cn/article_205.html
这次测试的数据库记录总数为100万,id字段是主键。测试是在SQL Server 2005下进行的(主要row_number函数是2005新增的)。
分页方法分别是MinMax法,两次Top法,Row_Number法1,Row_Number法2。示例语句如下:
-- MinMax法
Select Top 10 * From T1000000 Where id<(
Select Min(id) From (
Select Top 100 id From T1000000 order By id Desc
) As T
) order By id Desc
-- 两次Top法
Select * From (
Select Top 10 * From (
Select Top 100 * From T1000000 order By id Desc
) As T order By id Asc
) As T order By id Desc
--Row_Number法1
Select * From (
Select *,row_number() over(order by id desc) as rownum From T1000000
) As T Where rownum between 91 And 100 order By id Desc
-- Row_Number法2
Select * From (
Select top 10010 *,row_number() over(order by id desc) as rownum From T1000000
) As T Where rownum>10000 order By id Desc
测试分页大小为10条记录每页,分别测第11页,第1001页,第50001页
CPU占用 | 逻辑读 | 耗时(毫秒) | |
MinMax法 | 0 | 43 | 1 |
两次Top法 | 0 | 40 | 1 |
Row_Number法1 | 0 | 42 | 2 |
Row_Number法2 | 0 | 42 | 2 |
CPU占用 | 逻辑读 | 耗时(毫秒) | |
MinMax法 | 0 | 63 | 1 |
两次Top法 | 0 | 57 | 1 |
Row_Number法1 | 0 | 253 | 2 |
Row_Number法2 | 0 | 253 | 3 |
CPU占用 | 逻辑读 | 耗时(毫秒) | |
MinMax法 | 16 | 1131 | 17 |
两次Top法 | 16 | 1131 | 17 |
Row_Number法1 | 16 | 1126 | 18 |
Row_Number法2 | 16 | 1126 | 16 |
按结果,我一直以为MinMax法用在主键上会最快,不过实测后发现两次Top法会更好。两种用到Row_Number的分页法其实效率一致,看来MS做过专门的优化。
如果是SQL Server 2005,还是用row_number,比较方便。2000的话就用两次Top(如果排序的不是唯一键,可以用Not In法或临时表法)。