SQL Server 2008中SQL应用系列--目录索引

前些天看到一篇文章《SQL Server 2012 - Server side paging demo using OFFSET/FETCH NEXT》,原文地址。作者在文中称,要SQL Server 2012使用OFFSET/FETCH NEXT分页,比SQL Server 2005/2008中的RowNumber()有显著改进。今天特地作了简单测试。现将过程分享如下:

附:我的测试环境为:
SQL Server 2012,命名实例

Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) 
Feb 10 2012 19:13:17 
Copyright (c) Microsoft Corporation
Enterprise Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)

沿用上文的测试数据库和表:

[sql] view plain copy
 
 print?
  1. IF OBJECT_ID('DemoPager2012') IS NOT NULL  
  2. DROP DataBase DemoPager2012  
  3. GO  
  4.   
  5. CREATE Database DemoPager2012  
  6. GO  
  7.   
  8. USE DemoPager2012  
  9. GO/*  
  10. Setup script to create the sample table and fill it with  
  11. sample data.  
  12. */  
  13. IF OBJECT_ID('Customers','U') IS NOT NULL  
  14. DROP TABLE Customers  
  15.   
  16. CREATE TABLE Customers ( CustomerID INT primary key identity(1,1),  
  17. CustomerNumber CHAR(4),  
  18. CustomerName VARCHAR(50),  
  19. CustomerCity VARCHAR(20) )  
  20. GOTRUNCATE table Customers  
  21. GO  
  22.   
  23. DBCC DROPCLEANBUFFERS  
  24. DBCC FREEPROCCACHE  
  25.   
  26. /*****运用CTE递归插入,速度较快,邀月注***********************/  
  27. WITH Seq (num,CustomerNumber, CustomerName, CustomerCity) AS  
  28. (SELECT 1,cast('0000'as CHAR(4)),cast('Customer 0' AS NVARCHAR(50)),cast('X-City' as NVARCHAR(20))  
  29. UNION ALL  
  30. SELECT num + 1,Cast(REPLACE(STR(num, 4), ' ', '0') AS CHAR(4)),  
  31. cast('Customer ' + STR(num,6) AS NVARCHAR(50)),  
  32. cast(CHAR(65 + (num % 26)) + '-City' AS NVARCHAR(20))  
  33. FROM Seq  
  34. WHERE num <= 10000  
  35. )  
  36. INSERT INTO Customers (CustomerNumber, CustomerName, CustomerCity)  
  37. SELECT CustomerNumber, CustomerName, CustomerCity  
  38. FROM Seq  
  39. OPTION (MAXRECURSION 0)  


插入1万条数据后,在SQL Server 2008 R2中执行Row_Number():

[sql] view plain copy
 
 print?
  1. /*  
  2. Server side paging demo using ROW_NUMBER() - SQL Server  
  3. 2005/2008 version.  
  4. */  
  5.   
  6. DBCC DROPCLEANBUFFERS  
  7. DBCC FREEPROCCACHE  
  8.   
  9. SET STATISTICS IO ON;  
  10. SET STATISTICS TIME ON;  
  11. GO  
  12.   
  13.   
  14. DECLARE @page INT, @size INT  
  15. SELECT @page = 3, @size = 10  
  16.   
  17. ;WITH cte AS (  
  18. SELECT TOP (@page * @size)  
  19. CustomerID,  
  20. CustomerName,  
  21. CustomerCity,  
  22. ROW_NUMBER() OVER(ORDER BY CustomerName ) AS Seq,  
  23. COUNT(*) OVER(PARTITION BY '') AS Total  
  24. FROM Customers  
  25. WHERE CustomerCity IN ('A-City','B-City')  
  26. ORDER BY CustomerName ASC  
  27. )  
  28. SELECT * FROM cte  
  29. WHERE seq BETWEEN (@page - 1 ) * @size + 1 AND @page * @size  
  30. ORDER BY seq;  
  31. GO  
  32.   
  33. SET STATISTICS IO OFF ;  
  34. SET STATISTICS TIME OFF;  
  35. GO  

SQL Server 2012中执行OFFSET/FETCH NEXT语句如下:

[sql] view plain copy
 
 print?
  1. /*  
  2. Server side paging demo using the new enhancements added  
  3. in SQL Server 2012  
  4. */  
  5. DBCC DROPCLEANBUFFERS  
  6. DBCC FREEPROCCACHE  
  7.   
  8. SET STATISTICS IO ON;  
  9. SET STATISTICS TIME ON;  
  10. GO  
  11.   
  12.   
  13. DECLARE @page INT, @size INT  
  14. SELECT @page = 3, @size = 10  
  15.   
  16. SELECT  
  17. *,  
  18. COUNT(*) OVER(PARTITION BY '') AS Total  
  19. FROM Customers  
  20. WHERE CustomerCity IN ('A-City','B-City')  
  21. ORDER BY CustomerID  
  22. OFFSET (@page -1) * @size ROWS  
  23. FETCH NEXT @size ROWS ONLY;  
  24. GO  
  25.   
  26. SET STATISTICS IO OFF;  
  27. SET STATISTICS TIME OFF;  
  28. GO  



 

在SQL Server 2012中执行如下语句:

[sql] view plain copy
 
 print?
  1. DBCC DROPCLEANBUFFERS  
  2. DBCC FREEPROCCACHE  
  3.   
  4. SET STATISTICS IO ON;  
  5. SET STATISTICS TIME ON;  
  6. GO  
  7.   
  8. DECLARE @page INT, @size INT  
  9. SELECT @page = 3, @size = 10  
  10.   
  11. ;WITH cte AS (  
  12. SELECT TOP (@page * @size)  
  13. CustomerID,  
  14. CustomerName,  
  15. CustomerCity,  
  16. ROW_NUMBER() OVER(ORDER BY CustomerName ) AS Seq,  
  17. COUNT(*) OVER(PARTITION BY '') AS Total  
  18. FROM Customers  
  19. WHERE CustomerCity IN ('A-City','B-City')  
  20. ORDER BY CustomerName ASC  
  21. )  
  22. SELECT * FROM cte  
  23. WHERE seq BETWEEN (@page - 1 ) * @size + 1 AND @page * @size  
  24. ORDER BY seq;  
  25.   
  26.   
  27. SELECT  
  28. *,  
  29. COUNT(*) OVER(PARTITION BY '') AS Total  
  30. FROM Customers  
  31. WHERE CustomerCity IN ('A-City','B-City')  
  32. ORDER BY CustomerID  
  33. OFFSET (@page -1) * @size ROWS  
  34. FETCH NEXT @size ROWS ONLY;  
  35. GO  
  36.   
  37. SET STATISTICS IO OFF;  
  38. SET STATISTICS TIME OFF;  
  39. GO  

 

结论:无论是从逻辑读取数还是响应时间实际执行行数等关键参数看,SQL Server 2012提供的OFFSET/FETCH NEXT分页方式都比Row_Number()方式有了较大的提升。

 

 

邀月注:本文版权由邀月和CSDN共同所有,转载请注明出处。
助人等于自助!   3w@live.cn
posted on 2016-07-26 16:06  风浪  阅读(1655)  评论(0编辑  收藏  举报