【讨论】SQL2005数据分页的几种方式效率问题

Question

我接触数据库时间比较晚，而且不会用存储过程——或者说暂时不想学。
纯语句分页的方式，一直用ROW_NUMBER()。（假设数据库符合第三范式要求）例如SELECT * FROM (SELECT [col1],[col2],ROW_NUMBER() OVER (ORDER BY [time] DESC) AS POS FROM [table1]) AS T WHERE T.POS BETWEEN 10001 AND 10100
今天在完善数据库连接类的时候，想到DataAdapter的Fill方法可以带startRecord和maxRecords参数，于是做了个测试。string sql = "SELECT TOP(400080) [col1],[col2] FROM [table1] ORDER BY [time] DESC";
//...
SqlDataAdapter sda = new SqlDataAdapter();
sda.SelectCommand = ...
//....
DataSet ds = new DataSet();
sda.Fill(ds,400000,50,"temp");
//...这样选取速度非常快。所以我想到了以前网上看到一个sql2000的分页的例子。因为2000没有ROW_NUMBER()函数，所以是这么写的：SELECT TOP 10000 [col1],[col2] FROM [table1] WHERE [pk] NOT IN(SELECT TOP 9900 [pk] FROM [table1])
DataAdapter是使用游标来读取的吗？还是有些内部的技术支持，对查询mssql做了优化呢？
就我使用的感受来讲，第一种是效率最低了，越往后翻越慢。第二种速度上总体比较一致。第三种没实际用过，那位前辈用过的话请说说经验。明天追分。好久没来了，分数富裕了。

Answer 1

顶一个先 
   SqlDataSource 分页很简单的

Answer 2

我只知道第三种当数据量大的话就不行了
有好多公司写SQL都不推荐用not in

Answer 3

SET ROWCOUNT 性能平均, 可操作性尚可
排序比较随意, 各分页情况下速度平均
in,nt in
 排序方式比较随意
资源开销比较大, 数据库承担不小的运算压力, 不适合做大表分页.

Answer 4

select top 10 * from (select top 20 * from tb order by _id asc) tmp order by _id desc

Answer 5

6楼这个大数据量能成吗？而且用两次order。
我看本书上讲，order之类不属于关系数据库概念范畴，这样的排序计算会很费资源啊。DataAdapter没有用过的吗？

Answer 6

SqlDataSource 支持下.还有不建议使用noi in条件.

Answer 7

跟SET ROWCOUNT什么关系呢？

Answer 8

用SqlDataSource的话，我还得引用WebControls？
看DataSource的方法和属性，基本和Adapter都一样的，猜测一下，应该也是封装了Adapter吧

Answer 9

ROW_NUMBER效率慢?我怎么没感觉，我觉得挺好的，给我现然用的存储过程Create Procedure [dbo].[PagingRecord] 
 @TableName varchar(50),        --表名
 @Fields varchar(5000) = '*',    --字段名(全部字段为*)
 @OrderField varchar(5000),        --排序字段(必须!支持多字段)
 @sqlWhere varchar(5000) = Null,--条件语句(不用加where)
 @pageSize int,                    --每页多少条记录
 @pageIndex int ,            --指定当前为第几页
 @TotalPage int OUT            --返回总页数 
as
--  declare @pageSize int
--  select @pageSize=40
begin
print '@PageSize='
print @PageSize
    Begin Tran --开始事务    Declare @sql nvarchar(4000);
    Declare @totalRecord int;        --计算总记录数
         
    if (@SqlWhere='' or @sqlWhere=NULL)
        set @sql = 'select @totalRecord = count(*) from ' + @TableName
    else
        set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere    EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数            --计算总页数
   
    if (@SqlWhere='' or @sqlWhere=NULL)
        set @sql = 'Select '+ @Fields +' FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName 
    else
        set @sql = 'Select '+ @Fields +' FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere        --处理页数超出范围情况
    if @PageIndex<=0
        Set @pageIndex = 1 select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
    if @pageIndex>@TotalPage
        Set @pageIndex = @TotalPage     --处理开始点和结束点
    Declare @StartRecord int
    Declare @EndRecord int
    print @pageIndex
    set @StartRecord = (@pageIndex-1)*@PageSize + 1    set @EndRecord = @StartRecord + @pageSize - 1
    --继续合成sql语句
    set @Sql = @Sql + ') as ' + @TableName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' +  Convert(varchar(50),@EndRecord)
    print @Sql
    Exec(@Sql)
    ---------------------------------------------------
If @@Error <> 0
       Begin
        RollBack Tran
        Return -1
       End
    Else
Begin
Commit Tran
     Return @totalRecord ---返回记录总数
End    
end

Answer 10

也许是因为我实际中多次join了表
但是Row_number翻到后面的时候，速度变慢是很明显的。或者说Row_number()的效率并不是很平均，不能保证是否在数据量到一个界限的时候用它分页不会超时。11楼这个，数据量在多少？