转SQL Server 存储过程的经典分页

tyxiayu

　　转自：http://www.iyunv.com/topic/230211
　　
　　平台与环境：
CPU:Intel(R) Pentium(R) Dual T2390 1.86GHz
内存：1G(系统正常启动后约占300M空间)
硬盘：SATA 160G 8M Cache
系统：windowsxp+Sql Server 2005 sp2
测试数据：共100万条



分页测试代码：
1)row_number的两种分页方式：分别用top和between过滤
2)包含子查询结果的三种分页方式
共5种方式。



方式1：
每页显示200条
分页至10万条之后的第两百条记录

Java代码

• PROCEDURE [dbo].[proc_select_moauser1]   
  
• AS   
  
• BEGIN   
  
•  SET NOCOUNT ON;   
  
• 
      declare @tdiff datetime   
  
• 
      set @tdiff=getdate()   
  
• 
      select top 200 * from(select row_number() over(order by uid asc) as rownumber,* from moa_user ) as tb where rownumber>100000  
  
• 
      select datediff(ms,@tdiff,getdate()) as '耗时(毫秒)'  
  
• END  
  

PROCEDURE [dbo].[proc_select_moauser1]
AS
BEGIN
SET NOCOUNT ON;
declare @tdiff datetime
set @tdiff=getdate()
select top 200 * from(select row_number() over(order by uid asc) as rownumber,* from moa_user ) as tb where rownumber>100000
select datediff(ms,@tdiff,getdate()) as '耗时(毫秒)'
END
　　
响应时间：156ms-210ms



方式2：
每页显示200条
分页至10万条之后的第两百条记录

Java代码

• PROCEDURE [dbo].[proc_select_moauser2]   
  
• AS   
  
• BEGIN   
  
•  -- SET NOCOUNT ON added to prevent extra result sets from   
  
•  -- interfering with SELECT statements.   
  
•  SET NOCOUNT ON;   
  
•   
  
• 
      -- Insert statements for procedure here   
  
• 
   declare @tdiff datetime   
  
• 
      set @tdiff=getdate()   
  
• 
      select * from(select row_number() over(order by uid asc) as rownumber,* from moa_user ) as tb where rownumber between 100000 and 100200  
  
• 
      select datediff(ms,@tdiff,getdate()) as '耗时(毫秒)'  
  
• END  
  

PROCEDURE [dbo].[proc_select_moauser2]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
declare @tdiff datetime
set @tdiff=getdate()
select * from(select row_number() over(order by uid asc) as rownumber,* from moa_user ) as tb where rownumber between 100000 and 100200
select datediff(ms,@tdiff,getdate()) as '耗时(毫秒)'
END
　　
响应时间：153ms-176ms



方式3
每页显示200条
分页至10万条之后的第两百条记录

Java代码

• PROCEDURE [dbo].[proc_select_moauser3]   
  
• AS   
  
• BEGIN   
  
•  -- SET NOCOUNT ON added to prevent extra result sets from   
  
•  -- interfering with SELECT statements.   
  
•  SET NOCOUNT ON;   
  
• 
     declare @tdiff datetime   
  
• 
      set @tdiff=getdate()   
  
• 
      select top 200  * from moa_user a  where uid  not in(select top 100000  uid  from moa_user  b order by uid)   
  
• 
      select datediff(ms,@tdiff,getdate()) as '耗时(毫秒)'  
  
• END  
  

PROCEDURE [dbo].[proc_select_moauser3]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @tdiff datetime
set @tdiff=getdate()
select top 200  * from moa_user a  where uid  not in(select top 100000  uid  from moa_user  b order by uid)
select datediff(ms,@tdiff,getdate()) as '耗时(毫秒)'
END
　　

响应时间：270ms-290ms


方式4：
每页显示200条
分页至10万条之后的第两百条记录

Java代码

• PROCEDURE [dbo].[proc_select_moauser4]   
  
• AS   
  
• BEGIN   
  
•  -- SET NOCOUNT ON added to prevent extra result sets from   
  
•  -- interfering with SELECT statements.   
  
•  SET NOCOUNT ON;   
  
• 
      declare @tdiff datetime   
  
• 
      set @tdiff=getdate()   
  
• 
      -- Insert statements for procedure here   
  
• 
     select * from ( select TOP 200 * FROM ( SELECT TOP 100000 * from moa_user ORDER BY uid ASC ) as amoaUser ORDER BY uid DESC ) as bmoaUser ORDER BY uid ASC   
  
• 
      select datediff(ms,@tdiff,getdate()) as '耗时(毫秒)'      
  
• END  
  

PROCEDURE [dbo].[proc_select_moauser4]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @tdiff datetime
set @tdiff=getdate()
-- Insert statements for procedure here
select * from ( select TOP 200 * FROM ( SELECT TOP 100000 * from moa_user ORDER BY uid ASC ) as amoaUser ORDER BY uid DESC ) as bmoaUser ORDER BY uid ASC
select datediff(ms,@tdiff,getdate()) as '耗时(毫秒)'   
END
　　响应时间：950ms



方式5：
每页显示200条
分页至10万条之后的第两百条记录

Java代码

• PROCEDURE [dbo].[proc_select_moauser5]   
  
•     
  
• AS   
  
• BEGIN   
  
•  -- SET NOCOUNT ON added to prevent extra result sets from   
  
•  -- interfering with SELECT statements.   
  
•  SET NOCOUNT ON;   
  
•   
  
• 
      -- Insert statements for procedure here   
  
• 
   declare @tdiff datetime   
  
• 
      set @tdiff=getdate()   
  
• 
      -- Insert statements for procedure here   
  
• 
     SELECT TOP 200 * FROM moa_user WHERE (uid > (SELECT MAX(uid) FROM (SELECT TOP 100000 uid FROM moa_user ORDER BY uid) AS temp_moa_user)) ORDER BY uid   
  
• 
      select datediff(ms,@tdiff,getdate()) as '耗时(毫秒)'      
  
• END  
  

PROCEDURE [dbo].[proc_select_moauser5]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
declare @tdiff datetime
set @tdiff=getdate()
-- Insert statements for procedure here
SELECT TOP 200 * FROM moa_user WHERE (uid > (SELECT MAX(uid) FROM (SELECT TOP 100000 uid FROM moa_user ORDER BY uid) AS temp_moa_user)) ORDER BY uid
select datediff(ms,@tdiff,getdate()) as '耗时(毫秒)'   
END
　　
响应时间：135ms