如何在数据层进行分页以提高性能
如何在数据层进行分页以提高性能(转)
在读取大量数据的时候我们可以通过DataReader对数据进行分页以提高性能,还有一个更好的方法就是在存储过程中对数据进行分页。
假设有一个Products表字段有(ProductID,Name,Description, Price)
以下方法只支持SQLServer 2005 因为ROW_NUMBER()函数是SQLServer 2005新增的。
1
CREATE PROCEDURE GetProducts
2![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
(@DescriptionLength INT, --定义参数:描述长度
4![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
@PageNumber INT, --页码
6![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
@ProductsPerPage INT, --每页产品数
8![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
@HowManyProducts INT OUTPUT) --产品总数
10![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
AS
12![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
-- 定义一个Table变量
14![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
DECLARE @Products TABLE
16![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
(RowNumber INT,
18![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
ProductID INT,
20![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
Name VARCHAR(50),
22![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
Description VARCHAR(5000)
24![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
Price MONEY)
26![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
-- 把数据读到刚定义的@Products 中
28![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
INSERT INTO @Products
30![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
SELECT ROW_NUMBER() OVER (ORDER BY Product.ProductID),
32![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
ProductID, Name,
34![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
SUBSTRING(Description, 1, @DescriptionLength) + '
' AS Description, Price,
36![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
FROM Product
38![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
-- 返回产品数
40![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
41
SELECT @HowManyProducts = COUNT(ProductID) FROM @Products
42![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
43
-- 返回请求页面的数据
44![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
45
SELECT ProductID, Name, Description, Price
46![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
47
FROM @Products
48![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
49
WHERE RowNumber > (@PageNumber - 1) * @ProductsPerPage
50![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
51
AND RowNumber <= @PageNumber * @ProductsPerPage
52![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/dot.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
46
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
47
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
48
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
49
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
50
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
51
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
52
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
在SQLServer 2000中可以用以下的方法:
1
CREATE PROCEDURE GetProducts
2![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
(@DescriptionLength INT, --定义参数:描述长度
4![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
@PageNumber INT, --页码
6![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
@ProductsPerPage INT, --每页产品数
8![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
@HowManyProducts INT OUTPUT) --产品总数
10![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
AS
12![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
-- 定义一个Table变量
14![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
DECLARE #Products TABLE --这里一定要用‘#’(声明为本地临时表)
16![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
(RowNumber SMALLINT NOT NULL IDENTITY(1,1), --类型一定要自动递增
18![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
ProductID INT,
20![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
Name VARCHAR(50),
22![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
Description VARCHAR(5000)
24![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
Price MONEY)
26![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
-- 把数据读到刚定义的#Products 中
28![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
INSERT INTO #Products (ProductID, Name, Description, Price)
30![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
SELECT
32![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
ProductID, Name,
34![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
SUBSTRING(Description, 1, @DescriptionLength) + '
' AS Description, Price,
36![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
FROM Product
38![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
-- 返回产品数
40![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
41
SELECT @HowManyProducts = COUNT(ProductID) FROM #Products
42![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
43
-- 返回请求页面的数据
44![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
45
SELECT ProductID, Name, Description, Price
46![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
47
FROM #Products
48![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
49
WHERE RowNumber > (@PageNumber - 1) * @ProductsPerPage
50![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
51
AND RowNumber <= @PageNumber * @ProductsPerPage
52![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/dot.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
46
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
47
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
48
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
49
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
50
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
51
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
52
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
大家都清楚了吧,这种方法比在DataReader中速度高效。