mssql sqlserver set rowCount row_number两种分页方法效率对比


摘要:
下文通过将通过实例对set rowcount 设置行数的分页和 row_number的分页


set rowCount和row_number分页原理简介
SQL Server具有Set Rowcount设置,这意味着命令处理在响应指定的行数后停止处理命令。
使用这个特性,我们可以使用此属性来达到分页的目的,实现思路:
1.根据每页的尺寸和当前需返回的页码,采用top命令按照倒序返回所有数据
2.在执行top命令前,设置rowcount属性,使sqlserver返回相应的行,来达到分页的目的
—————————————————————–
sql server 中我们可以使用row_number对所有数据进行编号,然后获取编号的指定段的数据

 例:获取第三页数据 每页20条数据
    数据表 100万行数据
 ----set rowcount分页获取数据获取方法
 DBCC DROPCLEANBUFFERS ---清理缓存
 set statistics io  on  ---查看逻辑读次数

declare @ruChangDate datetime ---分页排序条件字段
set rowCount 40 
 
select ruchangDate into #t   from  tableName(nolock) 
order by ruChangDate desc    ----获取前
 
 select @ruchangDate =min(ruchanGDate) from #t   ---获取前2页数据中,排序字段的最小值

set rowcount 20
select  * from tableName(nolock) where ruchangDate <=@ruChangDate
 order by ruChangDate desc    ---输出第三页数据
  
 set rowCount 0
 
 truncate table #t
 drop table #t 

 ---row_number分页数据获取方法
   DBCC DROPCLEANBUFFERS
 set statistics io  on 
select * from 
(
select row_number() over(order by ruchangDate desc )as keyId ,* from tableName(nolock))
 as t 
 where t.keyId between 40 and 60 
go


1.逻辑读取分析
set rowCount 分页涉及逻辑读取 23次
row_number分页涉及逻辑读取 65次,从逻辑读取的次数上,我们可以看出set rowcount分页方式优于row_number
并且这个数值会随着 页数的增加,row_number逻辑读取的次数增加,而set rowcount逻辑不会增加
----------------------------------------------------------------------------------------------
2. 查询计划上分析
如果排序字段上设置了索引,那么set rowcount会使用索引查找
由于row_number需要为全表进行编号排序,所以使用索引扫描
综上所述:
我们可以看出set rowcount分页效率明显高于row_number。

mssql_sqlserver_set_rowCount

mssql_sqlserver_set_rowCount


mssql_sqlserver_set_rowcount分页执行计划

mssql_sqlserver_set_rowcount分页执行计划


----------------------------------------------------------------------
mssql_sqlserver_row_number分页逻辑读次数

mssql_sqlserver_row_number分页逻辑读次数


mssql_sqlserver_row_number分页执行计划

mssql_sqlserver_row_number分页执行计划