大数据的通用分页总结！

个人总结：

本人以前写的一篇ASP.NET通用分页程序 用到的“通用分页存储过程”在视图下没办法走索引效率很低，在大数据时很明显，现换以下存储过程：

 

ALTER   procedure   sp_pagesite   
  (   
  /*   
  @Tables           表名                                         必选     
  @PrimaryKey   主关键字                                 必选   
  @pagesize       页码大小                                 可选     默认值：1   
  @pageindex     当前页                                     可选     默认值：1   
  @docount         是否只统计总记录数             可选   默认值：否   
  @Fields           选择字段                                 可选   默认：所有字段   
  @Sort               排序语句，不带Order   By     可选   
  @Filter           过滤语句，不带Where           可选   
  */   
  @Tables   nvarchar(400),   
  @PrimaryKey   nvarchar(100),   
  @pagesize   int=1,   
  @pageindex   int=1,   
  @docount   bit=0,   
  @Fields   varchar(1000)   =   '*',   
  @Sort   varchar(1000)   =   NULL,   
  @Filter   varchar(1000)   =   NULL)   
  as   
  set   nocount   on   
    
  DECLARE   @strFilter   varchar(1000)   
  DECLARE   @strSort   varchar(1000)   
  IF   @Filter   IS   NOT   NULL   AND   @Filter   !=   ''   
  BEGIN   
  SET   @strFilter   =   '   WHERE   '   +   @Filter   +   '   '   
  END   
  ELSE   
  BEGIN   
  SET   @strFilter   =   ''   
  END   
  IF   @Sort   IS   Not   NULL   And   @Sort!=''   
                  Begin   
                                  Set   @strSort='order   by   '+@Sort+''   
                  End   
                            
  if(@docount=1)   
    
  exec('select   count('+@PrimaryKey+')   from   '+@Tables+'   '+@strFilter+'')   
  else   
  begin   
  declare   @PageLowerBound   int   
  declare   @PageUpperBound   int   
  set   @PageLowerBound=(@pageindex-1)*@pagesize   
  set   @PageUpperBound=@PageLowerBound+@pagesize   
  create   table   #pageindex(id   int   identity(1,1)   not   null,nid   int)   
  set   rowcount   @PageUpperBound   
  exec(   
  '   
  insert   into   #pageindex(nid)   
  select   '+@PrimaryKey+'   from   '+@Tables+'   '+@strFilter+'   '+@strSort+'   
  select   O.'+@Fields+'   
  from   '+@Tables+'   O,#pageindex   p   
  where   O.'+@PrimaryKey+'=p.nid   and   p.id>'+@PageLowerBound+'   and   p.id<='+@PageUpperBound+'   order   by   p.id   
  '   
  ) 
  drop table #PageIndex  
  end   
  set   nocount   off   

注：此存储过程需在一个主健（PrimaryKey）为索引，而ROW_NUMBER()不用，各有各的好处，看情况而用！

在非视图下，用ROW_NUMBER分页时(Row_Number()虽然它必须先为10万条记条生成RowNumber,但是在百万级数据下效率不会很差)，用日期排序只要不把大文本数据读出来效率还是行的。或者用id进行排序那把大文本数据读出来也还行。
而上面的方法就只要提供一个主健（PrimaryKey）为索引不管是视图还是表读取效率都还行。

附文章：SQL Server 2005下的分页SQL

 

其实基本上有三种方法：

1、使用SQL Server 2005中新增的ROW_NUMBER

几种写法分别如下：

 

1SELECT TOP 20 * FROM (SELECT
2   ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber,
3   *
4FROM
5   dbo.mem_member) _myResults
6WHERE
7   RowNumber > 10000
8

 

1SELECT * FROM (SELECT
2   ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber,
3   *
4FROM
5   dbo.mem_member) _myResults
6WHERE
7   RowNumber between 10000 and 10020

 

1WITH OrderedResults AS 
2
3(SELECT *, ROW_NUMBER() OVER (order by Namec) as RowNumber FROM dbo.mem_member)
4
5SELECT * 
6
7FROM OrderedResults
8
9WHERE RowNumber between 10000 and 10020

不管哪种写法，性能都不理想。在8,9万条数据的情况下要运行6秒左右。

2、使用临时表再加存储过程

 1BEGIN
 2                DECLARE @PageLowerBound int
 3                DECLARE @PageUpperBound int
 4                
 5                -- Set the page bounds
 6                SET @PageLowerBound = 10000
 7                SET @PageUpperBound = 10020
 8
 9                -- Create a temp table to store the select results
10                Create Table #PageIndex
11                (
12                    [IndexId] int IDENTITY (1, 1) NOT NULL,
13                    [Id] varchar(18) 
14                )
15                
16                -- Insert into the temp table
17                declare @SQL as nvarchar(4000)
18                SET @SQL = 'INSERT INTO #PageIndex (Id)'
19                SET @SQL = @SQL + ' SELECT'
20                SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
21                SET @SQL = @SQL + ' m_id'
22                SET @SQL = @SQL + ' FROM dbo.mem_member'
23                SET @SQL = @SQL + ' ORDER BY NameC'
24                
25                -- Populate the temp table
26                exec sp_executesql @SQL
27
28                -- Return paged results
29                SELECT O.*
30                FROM
31                    dbo.mem_member O,
32                    #PageIndex PageIndex
33                WHERE
34                    PageIndex.IndexID > @PageLowerBound
35                    AND O.[m_Id] = PageIndex.[Id]
36                ORDER BY
37                    PageIndex.IndexID
38                
39drop table #PageIndex            
40                END

而使用这种方法，在同样的情况下用时只需1秒。
看样子，row_number是个鸡肋。

3、如果觉得临时表不好，还可以使用SET ROWCOUNT

 1begin
 2DECLARE @first_id varchar(18), @startRow int
 3    
 4SET ROWCOUNT 10000
 5SELECT @first_id = m_id FROM mem_member ORDER BY m_id
 6
 7SET ROWCOUNT 20
 8
 9SELECT m.* 
10FROM mem_member m
11WHERE m_id >= @first_id
12ORDER BY m.m_id
13
14SET ROWCOUNT 0
15end

 

不过，这种方法有缺点。按ID排序就快，按其他字段排序就慢。