如何在数据层分页以提高性能

 

    在读取大量数据的时候我们可以通过DataReader对数据进行分页以提高性能，还有一个更好的方法就是在存储过程中对数据进行分页。

    假设有一个Products表字段有(ProductID,Name,Description, Price)

以下方法只支持SQLServer 2005 因为ROW_NUMBER()函数是SQLServer 2005新增的。

 1CREATE PROCEDURE GetProducts
 2
 3(@DescriptionLength INT,           --定义参数：描述长度
 4
 5@PageNumber INT,                --页码
 6
 7@ProductsPerPage INT,             --每页产品数 
 8
 9@HowManyProducts INT OUTPUT)  --产品总数
10
11AS
12
13-- 定义一个Table变量
14
15DECLARE @Products TABLE
16
17(RowNumber INT,
18
19 ProductID INT, 
20
21 Name VARCHAR(50), 
22
23 Description VARCHAR(5000)
24
25Price MONEY)
26
27-- 把数据读到刚定义的@Products 中
28
29INSERT INTO @Products    
30
31SELECT ROW_NUMBER() OVER (ORDER BY Product.ProductID), 
32
33       ProductID, Name, 
34
35       SUBSTRING(Description, 1, @DescriptionLength) + '' AS Description, Price,
36
37FROM Product 
38
39-- 返回产品数
40
41SELECT @HowManyProducts = COUNT(ProductID) FROM @Products
42
43-- 返回请求页面的数据
44
45SELECT ProductID, Name, Description, Price
46
47FROM @Products
48
49WHERE RowNumber > (@PageNumber - 1) * @ProductsPerPage 
50
51  AND RowNumber <= @PageNumber * @ProductsPerPage
52
53

 

在SQLServer 2000中可以用以下的方法：

  

CREATE PROCEDURE GetProducts

(@DescriptionLength INT,           --定义参数：描述长度

@PageNumber INT,                --页码

@ProductsPerPage INT,             --每页产品数

@HowManyProducts INT OUTPUT) --产品总数

AS

-- 定义一个Table变量

DECLARE #Products TABLE           --这里一定要用‘#’（声明为本地临时表）

(RowNumber SMALLINT NOT NULL IDENTITY(1,1),    --类型一定要自动递增

 ProductID INT,

 Name VARCHAR(50),

 Description VARCHAR(5000)

Price MONEY)

-- 把数据读到刚定义的#Products 中

INSERT INTO #Products (ProductID, Name, Description, Price)  

SELECT

       ProductID, Name,

       SUBSTRING(Description, 1, @DescriptionLength) + '...' AS Description, Price,

FROM Product

-- 返回产品数

SELECT @HowManyProducts = COUNT(ProductID) FROM #Products

-- 返回请求页面的数据

SELECT ProductID, Name, Description, Price

FROM #Products

WHERE RowNumber > (@PageNumber - 1) * @ProductsPerPage

 AND RowNumber <= @PageNumber * @ProductsPerPage

 

大家都清楚了吧，这种方法比在DataReader中速度高效。