SQL数据分页技术

数据层分页技术

  • 在数据库查询时，只查询特定页面的数据
  • 主要由T-SQL来完成
  • 适用于比较大的数据表
  • 优点：返回的结果集小，查询速度快
  • 缺点：需要多次查询数据库

 

数据层数据分页五种常用方法：

其中Row_Number()方法可替代identity(1,1)，从而实现和临时表、表变量方法混和使用。

USE AdventureWorks2008

 

1、Top*Top

 1 --使用 Top*Top 字符串拼接可选
 2 DECLARE @Start datetime,@end datetime;
 3 SET @Start=getdate();
 4 
 5 DECLARE @PageNumber INT, @PageSize INT, @Sql varchar(max);
 6 SET @PageNumber=5000;
 7 SET @Pt=10;
 8 SET @Sql='SELECT T2.* FROM (
 9     SELECT TOP 10 T1.* FROM 
10         (SELECT TOP ' + STR(@PageNumber*@PageSize) +' * FROM Production.TransactionHistoryArchive
11         ORDER BY ReferenceOrderID ASC) AS T1
12     ORDER BY ReferenceOrderID DESC) AS T2
13 ORDER BY ReferenceOrderID ASC';
14 EXEC (@sql);
15 
16 SET @end=getdate();
17 PRINT Datediff(millisecond,@Start,@end);

2.not in

 1 --使用not in
 2 
 3 DECLARE @Start datetime,@end datetime;
 4 SET @Start=GETDATE();
 5 
 6 DECLARE @PageNumber INT,@PageSize INT,@Sql varchar(max);
 7 SET @PageNumber=5000;
 8 SET @PageSize=10;
 9 SELECT Top (@PageSize)  * FROM Production.TransactionHistoryArchive
10 WHERE ReferenceOrderID  NOT IN (
11     SELECT Top((@PageNumber-1)*@PageSize) ReferenceOrderID FROM Production.TransactionHistoryArchive 
12     ORDER BY ReferenceOrderID ASC) 
13 ORDER BY ReferenceOrderID ASC
14 
15 
16 SET @end=GETDATE();
17 
18 print datediff(millisecond,@Start,@end)
View Code

 

 

3、表变量

 1 --使用表变量
 2 DECLARE @Start datetime,@end datetime;
 3 SET @Start=getdate();
 4 DECLARE @PageNumber INT, @PageSize INT, @Sql varchar(max);
 5 SET @PageNumber=5000;
 6 SET @PageSize=10;
 7 
 8 DECLARE @local_variable table (RowNumber int identity(1,1),[TransactionID] [int],
 9     [ProductID] [int],
10     [ReferenceOrderID] [int],
11     [ReferenceOrderLineID] [int],
12     [TransactionDate] [datetime],
13     [TransactionType] [nchar](1),
14     [Quantity] [int],
15     [ActualCost] [money],
16     [ModifiedDate] [datetime]);
17 insert into @local_variable (TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate) 
18 SELECT TOP 50000 TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate from Production.TransactionHistoryArchive ORDER BY ReferenceOrderID ASC
19 select * from @local_variable where RowNumber > (@PageNumber-1)*@PageSize and RowNumber <= @PageNumber*@PageSize
20 
21 SET @end=getdate();
22 PRINT Datediff(millisecond,@Start,@end);

4、临时表

--使用临时表
DECLARE @Start datetime,@end datetime;
SET @Start=getdate();
DECLARE @PageNumber INT, @PageSize INT, @Sql varchar(max);
SET @PageNumber=5000;
SET @PageSize=10;

create table #local_variable(RowNumber int identity(1,1),[TransactionID] [int],
    [ProductID] [int],
    [ReferenceOrderID] [int],
    [ReferenceOrderLineID] [int],
    [TransactionDate] [datetime],
    [TransactionType] [nchar](1),
    [Quantity] [int],
    [ActualCost] [money],
    [ModifiedDate] [datetime]);
insert into #local_variable (TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate) 
SELECT TOP 50000 TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate from Production.TransactionHistoryArchive ORDER BY ReferenceOrderID ASC
select * from #local_variable where RowNumber > (@PageNumber-1)*@PageSize and RowNumber <= @PageNumber*@PageSize

SET @end=getdate();
PRINT Datediff(millisecond,@Start,@end);

5、ROW_NUMBER

 

--使用 ROW_NUMBER
DECLARE @Start datetime,@end datetime;
SET @Start=getdate();

DECLARE @PageNumber INT, @PageSize INT, @Sql varchar(max);
SET @PageNumber=5000;
SET @PageSize=10;
SELECT * FROM
( SELECT ROW_NUMBER()
OVER(ORDER BY ReferenceOrderID) AS RowNumber,
*
FROM Production.TransactionHistoryArchive) AS T
WHERE T.RowNumber<=@PageNumber*@PageSize AND T.RowNumber>(@PageNumber-1)*@PageSize;

SET @end=getdate();

posted @ 2015-05-23 14:43  willey01  阅读(146)  评论(0)    收藏  举报