MSSQL 存储过程分页优化-实验篇(一),分页效率提升十倍


/*新建测试表*/
create table A(keyId int identity(1,1),info varchar(36),info2 varchar(36),info3 varchar(36), info4 varchar(36))
go
/*创建聚集索引 不包含任何列*/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[A]’) AND name = N’IX_A’)
DROP INDEX [IX_A] ON [dbo].[A] WITH ( ONLINE = OFF )
GO
CREATE CLUSTERED INDEX [IX_A] ON [dbo].[A]
(
[keyId] ASC
)
go
/*初始化表A数据*/
declare @i int
set @i =0
while @i<100000 begin insert into A(info,info2,info3,info4)values(newId(),newid(),newid(),newid()) set @i=@i+1 end go


实验写法一:

 /*返回 DB 的逻辑读取 物理读取 页数*/

DBCC DROPCLEANBUFFERS    ---清除DB服务器缓存
go

set statistics io  on         ---打开逻辑读取参数

declare @rowAll int   ---总行数

declare @thisPage int  ---当前页码
declare @pageSize int  ---当前尺寸


set @thisPage =10
set @pageSize =100 

SELECT keyId,info,info2,info3,info4,
ROW_NUMBER() OVER(ORDER BY keyId DESC) AS rowNo INTO #t1
 FROM A(NOLOCK) WHERE keyId>1000
  
SET @rowAll  = @@ROWCOUNT  ---获取符合条件的总行数

/*返回符合条件的行*/
SELECT t2.* FROM A AS t2(NOLOCK), #t1 As t1  
WHERE t1.rowNo>@thisPage*@pageSize AND t1.rowNo<=(@thisPage+1)*@pageSize     ---分页
AND t1.keyId=t2.keyId      ---符合条件的数据

/*返回总条数*/ 
SELECT @rowAll as [总条数]    

----清除临时表
truncate table #t1
drop     table  #t1 


DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。
表 'A'。扫描计数 1,逻辑读取 2124 次,物理读取 12 次,预读 2106 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(99000 行受影响)
(100 行受影响)
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'A'。扫描计数 1,逻辑读取 2136 次,物理读取 5 次,预读 21 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 '#t1________________________________________________________________________________________________________________
_0000000000A0'。扫描计数 1,逻辑读取 2154 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)



实验写法二:

  /*返回 DB 的逻辑读取 物理读取 页数*/
DBCC DROPCLEANBUFFERS    ---清除DB服务器缓存
go
set statistics io  on         ---打开逻辑读取参数
declare @rowAll int   ---总行数
declare @thisPage int  ---当前页码
declare @pageSize int  ---当前尺寸

set @thisPage =10
set @pageSize =100 
SELECT keyId,
ROW_NUMBER() OVER(ORDER BY keyId DESC) AS rowNo INTO #t1
 FROM A(NOLOCK) WHERE keyId>1000
  
SET @rowAll  = @@ROWCOUNT  ---获取符合条件的总行数
/*返回符合条件的行*/
select keyId into #t3 from #t1  where #t1.rowNo>@thisPage*@pageSize AND #t1.rowNo<=(@thisPage+1)*@pageSize 
select * from A as t2(nolocK) 
where t2.keyId in (
select keyId from #t3   
)

/*返回总条数*/ 
SELECT @rowAll as [总条数]    

----清除临时表
truncate table #t1
drop     table  #t1 

truncate table #t3
drop     table  #t3  


DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。
表 'A'。扫描计数 1,逻辑读取 2125 次,物理读取 13 次,预读 2106 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(99000 行受影响)
表 '#t1_______________________________________________________________________________________________________
__________0000000000A1'。扫描计数 1,逻辑读取 259 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(100 行受影响)
(100 行受影响)
表 'A'。扫描计数 100,逻辑读取 349 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 '#t3___________________________________________________________________________________________________________
______0000000000A2'。扫描计数 1,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)



方法对比:
从实验的结果上,我们可以看出 ,方法二的分页效率比方法一分页效率快10倍左右;
导致效率的差异,在数据展示上 方法二上减少了一次 物理表A的全表扫描,是逻辑读取次数降低
方法二,在第一次创建临时表时,只选择了所需字段,降低了磁盘写的数据量

相关阅读:
聚集索引和非聚集索引的区别
如何查看MSSQL 语句 存储过程 消耗的逻辑读和物理读 扫描次数情况