SQL Server的分页存储过程

一直以来，分页读取海量数据是SQL Server的一个薄弱环节，为大家所诟病，相比而言，Oracle很早就实现了类似的功能。SQL Server 2005在此方面进行了很大的改进，但是好像一直没有看到完整实现的例子。本文希望能够给出最终方案。

为了有的放矢，先给出完整的需求：

l        采用存储过程，而非动态组合SQL

l        在给定范围内，查询条件是不定的

l        查询一张表或多张表

l        所查询的数据是海量的，少则十万，多则千万

l        排序的字段和方式是不定的

l        每页读取的数据行数是可变的

l        可以随机跳到指定的页和行，包括末页

l        知道总的页数

从网络上可以查到许多解决方案，如果按上述需求分析，没有一个能够满足全部要求。

在上述需求中，有总页数的要求，因此需要有一个专门的存储过程来统计总的行数，从而根据每页行数计算页数，并实现页面跳转的功能。这个存储过程不存在技术难度，无需列出。需要指出的是，这个存储过程执行的时间偶尔相当可观的，特别是对视图中的字段、没有索引的字段查询时甚至比分页的存储过程还要耗时，因此在应用时需注意。

每次在查询多个表时，都会建立关联，因此建立一个视图，由SQL Server2005来保存和优化此关联关系是必要的，也简化了具体的存储过程。所以本文将以单表为例进行介绍。表结构如下：

CREATE TABLE [dbo].[PRODUCT](

    [PRODUCT_ID] [nvarchar](50) NOT NULL,

    [PRODUCT_DESC] [nvarchar](100) NULL,

    [COST_PER_UNIT] [numeric](12, 6) NULL,

    [PRICE_PER_UNIT] [numeric](12, 6) NULL,

    [MODEL_TYPE] [nvarchar](50) NULL,

    [STATUS] [nvarchar](5) NULL,

 CONSTRAINT [PK_PRODUCT] PRIMARY KEY CLUSTERED

(

    [PRODUCT_ID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

 

首先需要处理的是有限范围内条件的不定问题。例如针对这个表，可以按PRODUCT_ID查询，也可以按PRODUCT_DESC查询，或者同时按PRODUCT_ID和PRODUCT_DESC查询。方法比较简单，例句如下：

PRODUCT_ID LIKE @PRODUCT_ID+'%' OR @PRODUCT_ID = '')

AND (PRODUCT_DESC LIKE @PRODUCT_DESC+'%' OR @PRODUCT_DESC = '')

此语句中，AND之前部分为按PRODUCT_ID查询，此查询中，OR之前部分为查询，后半部分关闭条件，即不按PRODUCT_ID查询。AND之后部分同理。需要指出的是，关闭条件的方法因数据类型、值的范围而有所不同。例如，所给出的例子为字符串类型，所查询的值没有空的，那么传入空值就表示不按这个条件查询，如果所查询的值可以为空，则必须指定一个值（本人不喜欢NULL，所以不把它作为选项）。这种条件模式对性能影响不大。

在存储过程中，最关键的部分是查询出所需要的数据范围，关键字PRODUCT_ID是一个不错的标记。对于视图，如果在这部分SQL中引用了全部字段，会导致读取不必要的数据，耗去相当多的时间，对于表影响不大。

这部分SQL并不难，已经有许多例子给出，采用的是TOP和ROW_NUMBER组合的方式，例句如下：

DECLARE @BEGIN_NUM INT,@END_NUM INT

SET @BEGIN_NUM=100000

SET @END_NUM=100030

SELECT *

FROM dbo.PRODUCT,

         (SELECT TOP (@END_NUM) PRODUCT_ID,ROW_NUMBER() OVER (ORDER BY PRODUCT_ID) AS RowNumber

          FROM dbo.PRODUCT

          ORDER BY PRODUCT_ID) A

WHERE dbo.PRODUCT.PRODUCT_ID=A.PRODUCT_ID

    AND A.RowNumber BETWEEN @BEGIN_NUM AND @END_NUM

现在如果加上排序，就基本实现完整的功能的。大部分情况下，排序部分只是排序字段的组合，所以大部分人没有想到如何实现动态排序。实际上，排序部分中可以加入表达式的。由于排序时只按所读取的字段进行排序，可以枚举出所有的排序情况，用CASE把它们组合起来，就可以灵活排序（特别指出，默认值同样需要注意）。完整的存储过程如下：

CREATE PROCEDURE spProductReadData(@PRODUCT_ID nvarchar(50), @PRODUCT_DESC nvarchar(100),@COST_PER_UNIT numeric(12,6),

    @PRICE_PER_UNIT numeric(12,6), @MODEL_TYPE nvarchar(50), @STATUS nvarchar(50),@BEGIN_NUM INT,@END_NUM INT,

    @SORT_COLUMN nvarchar(50),@SORT_TYPE nvarchar(10))

AS

SELECT *

FROM dbo.PRODUCT,

    (SELECT *,ROW_NUMBER() OVER (ORDER BY @SORT_COLUMN) AS RowNumber FROM

         (SELECT TOP (@END_NUM) PRODUCT_ID

          FROM dbo.PRODUCT

          WHERE (PRODUCT_ID LIKE @PRODUCT_ID+'%' OR @PRODUCT_ID = '')

            AND (PRODUCT_DESC LIKE @PRODUCT_DESC+'%' OR @PRODUCT_DESC = '')

            AND (COST_PER_UNIT = @COST_PER_UNIT OR @COST_PER_UNIT = 0)

            AND (PRICE_PER_UNIT = @PRICE_PER_UNIT OR @PRICE_PER_UNIT = 0)

            AND (MODEL_TYPE LIKE @MODEL_TYPE+'%' OR @MODEL_TYPE = '')

            AND (STATUS = @STATUS OR @STATUS = '')

          ORDER BY

            CASE WHEN @SORT_COLUMN='PRODUCT_ID' AND @SORT_TYPE='ASC' THEN PRODUCT_ID ELSE '' END ASC,

            CASE WHEN @SORT_COLUMN='PRODUCT_ID' AND @SORT_TYPE='DESC' THEN PRODUCT_ID ELSE '' END DESC,

            CASE WHEN @SORT_COLUMN='PRODUCT_DESC' AND @SORT_TYPE='ASC' THEN PRODUCT_DESC ELSE '' END ASC,

            CASE WHEN @SORT_COLUMN='PRODUCT_DESC' AND @SORT_TYPE='DESC' THEN PRODUCT_DESC ELSE '' END DESC,

            CASE WHEN @SORT_COLUMN='COST_PER_UNIT' AND @SORT_TYPE='ASC' THEN COST_PER_UNIT ELSE 0 END ASC,

            CASE WHEN @SORT_COLUMN='COST_PER_UNIT' AND @SORT_TYPE='DESC' THEN COST_PER_UNIT ELSE 0 END DESC,

            CASE WHEN @SORT_COLUMN='PRICE_PER_UNIT' AND @SORT_TYPE='ASC' THEN PRICE_PER_UNIT ELSE 0 END ASC,

            CASE WHEN @SORT_COLUMN='PRICE_PER_UNIT' AND @SORT_TYPE='DESC' THEN PRICE_PER_UNIT ELSE 0 END DESC,

            CASE WHEN @SORT_COLUMN='MODEL_TYPE' AND @SORT_TYPE='ASC' THEN MODEL_TYPE ELSE '' END ASC,

            CASE WHEN @SORT_COLUMN='MODEL_TYPE' AND @SORT_TYPE='DESC' THEN MODEL_TYPE ELSE '' END DESC,

            CASE WHEN @SORT_COLUMN='STATUS' AND @SORT_TYPE='ASC' THEN STATUS ELSE '' END ASC,

            CASE WHEN @SORT_COLUMN='STATUS' AND @SORT_TYPE='DESC' THEN STATUS ELSE '' END DESC

        )AA

    ) A

WHERE dbo.PRODUCT.PRODUCT_ID=A.PRODUCT_ID

    AND A.RowNumber BETWEEN @BEGIN_NUM AND @END_NUM

ORDER BY A.RowNumber

此存储过程虽然很长，但是性能还是相当不错的。在普通PC机上测试此存储过程，总数据量二百万的数据，如果符合查询条件的数据行数在10万左右，耗时在3秒左右。然而，一旦取出符合查询条件的数据行数在100万，由于需要排序的值太多，耗时升至9秒，因此需要根据情况进行取舍，但这种情况多半是没有意义的。

此外，根据取范围数据的SQL的特性，如果读取的是靠近末页的数据，最好换成将排序方式颠倒一下，例如将正序改为倒序。

 

 

参考：

 

使用sql2005的新特性分页的储存过程:Top,Row_Number