毫秒级百万数据分页存储过程

数据分页一直是一个经久不衰的话题,在园子里看到了N多高手,都比我厉害,

今天把我使用的分页代码继续分享;请高手手下留情,少拍砖啦哈哈;

欢迎大家转载!!!!

首先参考园子里的高手方法生成一个百万数据表; 耗时 33 秒;

然后创建分页存储过程;这个是基于SQL 2005 的ROW_NUMBER的, SQL 2000 不适用; SQL 2008 我没有安装,所以也没有测试过!估计应该可以运行.

 2种算法可以选择 1. 使用  BETWEEN   ;  2.  TOP  ; 代码中已经注释了.

我测试了一下2种方法基本没有什么区别;代码如下:

1. USE [DATA_TEMP] 
2. GO 
3. /****** 对象:  StoredProcedure [dbo].[im531_Page]    脚本日期: 11/26/2010 10:52:35 ******/ 
4. SET ANSI_NULLS ON 
5. GO 
6. SET QUOTED_IDENTIFIER ON 
7. GO 
8.  
9. -- ============================================= 
10. -- Author:      im531  
11. -- Create date: 2010-10-25 
12. -- Description: SQL 2005 ROW_NUMBER 分页 
13. -- Modify Date: 2010-10-26 
14. -- ============================================= 
15.  
16. CREATE PROCEDURE [dbo].[im531_Page] 
17.     @TabeName AS NVARCHAR(50), 
18.     @Fields AS NVARCHAR(1024), 
19.     @SearchWhere AS NVARCHAR(1024), 
20.     @OrderFields AS NVARCHAR(1024), 
21.     @pageNumber AS INT, 
22.     @page AS INT 
23. AS 
24. BEGIN 
25.     SET NOCOUNT ON; 
26.     DECLARE @sqlType AS TINYINT 
27.     SET @sqlType = 0  -- 0 BETWEEN   1 TOP 
28.     DECLARE @sql AS NVARCHAR(MAX) 
29.     IF @SearchWhere <> '' SET @SearchWhere = ' WHERE ' + @SearchWhere 
30.     IF @page < 2 SET @page = 1 
31.     IF @page = 1 
32.         BEGIN 
33.             SET @sql = 'SELECT TOP ' + CONVERT(NVARCHAR(20),@pageNumber) + ' ' + @Fields 
34.                 + 'FROM [' + @TabeName + '][a] WITH(NOLOCK) ' + @SearchWhere + ' ORDER BY ' + @OrderFields 
35.         END 
36.     ELSE 
37.         BEGIN 
38.             --临时表 
39.             SET @sql = ';WITH [Page_____Table] AS(' 
40.                 + 'SELECT ROW_NUMBER() OVER(ORDER BY ' + @OrderFields + ') AS [RowNow],' + @Fields 
41.                 + 'FROM ['+ @TabeName +' ][a] WITH(NOLOCK) ' + @SearchWhere 
42.                 + ')' 
43.             --查找当前页面记录 
44.             IF @sqlType = 0 
45.                 BEGIN 
46.                 -- A USE BETWEEN 
47.                     SET @sql = @sql + 'SELECT *' 
48.                         + 'FROM [Page_____Table] WITH(NOLOCK)' 
49.                         + 'WHERE [RowNow] BETWEEN ' + CONVERT(NVARCHAR(20),(@page - 1) * @pageNumber + 1) + ' AND ' + CONVERT(NVARCHAR(20),@page * @pageNumber) 
50.                         + 'ORDER BY [RowNow]' 
51.                 END 
52.             ELSE 
53.                 BEGIN 
54.                 -- B USE TOP 
55.                     SET @sql = @sql + 'SELECT TOP ' + CONVERT(NVARCHAR(20),@pageNumber) + ' *' 
56.                         + 'FROM [Page_____Table] WITH(NOLOCK)' 
57.                         + 'WHERE [RowNow] > ' + CONVERT(NVARCHAR(20),(@page - 1) * @pageNumber) 
58.                         + 'ORDER BY [RowNow]' 
59.                 END 
60.         END 
61.     EXEC(@sql) 
62. END 

然后测试分页性能;每页显示20条记录, 百万数据最大50000页,当前页采用随机数取值;

1. DECLARE @d DATETIME 
2. SET @d = GETDATE() 
3.  
4. DECLARE @M AS INT;DECLARE @N AS INT;DECLARE @cPage AS INT 
5. SET @M = 50000;SET @N = 1 
6. SET @cPage = CAST((rand()*(@M-@N)+@N) AS INT) 
7.  
8. EXECUTE [im531_Page] 
9.     @TabeName = 'Table_1', 
10.     @Fields = '[a],[b]', 
11.     @SearchWhere = '[a] > 0', 
12.     @OrderFields = '[a] DESC', 
13.     @pageNumber = 20, 
14.     @page = @cPage 
15.  
16.  
17. SELECT [当前页] = @cPage,[分页执行耗时间(毫秒)] = DATEDIFF(ms,@d,GETDATE()) 

在我的机器上面测试均为毫秒级!!!!

园子里的高手若有更好的方法欢迎分享一下!!!!!!!!!!!!!!!

--------------------------------------- 我是华丽的分割线 ---------------------------------------

回复10楼园友 ★金★ :

加上数据统计结果后稍慢了一丁点儿,估计在实际运用中可以忽略不计;