Sql数据层分页技术

看了一篇讲座，说到数据层分页技术，用到了4中方式，1）使用top *top   2)使用表变量  3）使用临时表 4）使用ROW_NUMBER函数。

其中最快的是第1 和第4中方式，接下来我们来看看这两种方式：

我们使用sql2005自带的数据库 AdventureWorks测试，

1）

--Use Top*Top

DECLARE @Start datetime,@end datetime;

SET @Start=getdate();

 

DECLARE @PageNumber INT, @Count INT, @Sql varchar(max);

SET @PageNumber=5000;

SET @Count=10;

SET @Sql='SELECT T2.* FROM (

    SELECT TOP 10 T1.* FROM

       (SELECT TOP ' + STR(@PageNumber*@Count) +' * FROM Production.TransactionHistoryArchive

       ORDER BY ReferenceOrderID ASC) AS T1

    ORDER BY ReferenceOrderID DESC) AS T2

ORDER BY ReferenceOrderID ASC';

EXEC (@sql);

 

SET @end=getdate();

PRINT Datediff(millisecond,@Start,@end);

GO

 

 

解析：我们是要查出第5000页，每页10条，也就是第49991~第50000条，

   先select出前50000条，再倒序出后10条，再升序排列，也就是49991~50000条，执行时间为373毫秒。

 

 

2）

--Use ROW_NUMBER

DECLARE @Start datetime,@end datetime;

SET @Start=getdate();

 

DECLARE @PageNumber INT, @Count INT, @Sql varchar(max);

SET @PageNumber=5000;

SET @Count=10;

SELECT * FROM

(   SELECT ROW_NUMBER() 

      OVER(ORDER BY ReferenceOrderID) AS RowNumber,   

      *

    FROM Production.TransactionHistoryArchive) AS T

WHERE T.RowNumber<=@PageNumber*@Count AND T.RowNumber>(@PageNumber-1)*@Count;

 

SET @end=getdate();

PRINT Datediff(millisecond,@Start,@end);

GO

 

解析：也是要查出第5000页，每页10条。先将数据全部排名，再where两个条件，一个是排名<=5000*10=50000条 并且排名>4999*10=49990条，也就是49991到50000条。 执行时间为156，这种方式更优。主要是top方式是反复的去查，消耗了时间。