USE AdventureWorks2008
GO
SELECT COUNT(*) FROM Production.TransactionHistoryArchive
GO
SELECT TOP 50 * FROM Production.TransactionHistoryArchive
ORDER BY ReferenceOrderID ASC
GO
--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
--USE table value
DECLARE @Start datetime,@end datetime;
SET @Start=getdate();
DECLARE @PageNumber INT, @Count INT, @Sql varchar(max);
SET @PageNumber=5000;
SET @Count=10;
DECLARE @local_variable table (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)*@Count and RowNumber <= @PageNumber*@Count
SET @end=getdate();
PRINT Datediff(millisecond,@Start,@end);
GO
--USE temp table
DECLARE @Start datetime,@end datetime;
SET @Start=getdate();
DECLARE @PageNumber INT, @Count INT, @Sql varchar(max);
SET @PageNumber=5000;
SET @Count=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)*@Count and RowNumber <= @PageNumber*@Count
SET @end=getdate();
PRINT Datediff(millisecond,@Start,@end);
GO
--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
Asp.net分页存储过程_Demo
4星 · 超过85%的资源 需积分: 0 17 浏览量
2008-10-20
09:57:25
上传
评论
收藏 28KB ZIP 举报 
_Demo.zip （22个子文件） 
Codes Codes 01.DL 
DataPager.sql 3KB 02.AL PagerSite App_Code AdwWks.dbml 3KB ProductUtil.cs 741B AdwWks.designer.cs 16KB AdwWks.dbml.layout 737B UsingDataPager.aspx 4KB UsingLINQ.aspx 2KB UsingGridView.aspx.cs 287B UsingGridView.aspx 2KB web.config 8KB App_Data UsingLINQ.aspx.cs 1KB UsingDataPager.aspx.cs 577B 03.CL PagerSite DataService.debug.js 38KB App_Code Northwind.edmx 6KB Northwind.Designer.cs 13KB NorthwindService.cs 695B DataServices.aspx.cs 286B AJAXPager.aspx.cs 283B web.config 9KB AJAXPager.aspx 2KB App_Data NorthwindService.svc 118B DataServices.aspx 3KB
评论2