垃圾回收站

June 11, 2007

简单谈基于SQL SERVER 分页存储过程的演进

[ 分类: SQLServer ] 由弗里曼·潘发表于 7:03 am

作者：郑佐 http://blog.csdn.net/vscn/archive/2006/04/01/646867.aspx
日期：2006-9-30

针对数据库数据在UI界面上的分页是老生常谈的问题了，网上很容易找到各种“通用存储过程”代码，而且有些还定制查询条件，看上去使用很方便。笔者打算通过本文也来简单谈一下基于SQL SERVER 2000的分页存储过程，同时谈谈SQL SERVER 2005下分页存储过程的演进。

在进行基于UI显示的数据分页时，常见的数据提取方式主要有两种。第一种是从数据库提取所有数据然后在系统应用程序层进行数据分页，显示当前页数据。第二种分页方式为从数据库取出需要显示的一页数据显示在UI界面上。

以下是笔者对两种实现方式所做的优缺点比较，针对应用程序编写，笔者以.NET技术平台为例。

类别	SQL语句	代码编写	设计时	性能
第一种	语句简单，兼容性好	很少	完全支持	数据越大性能越差
第二种	看具体情况	较多	部分支持	良好，跟SQL语句有关

对于第一种情况本文不打算举例，第二种实现方式笔者只以两次TOP方式来进行讨论。
在编写具体SQL语句之前，定义以下数据表。
数据表名称为：Production.Product。Production为SQL SERVER 2005中改进后的数据表架构，对举例不造成影响。
包含的字段为：

列名	数据类型	允许空	说明
ProductID	Int		产品ID，PK。
Name	Nvarchar(50)		产品名称。

不难发现以上表结构来自SQL SERVER 2005 样例数据库AdventureWorks的Production.Product表，并且只取其中两个字段。

分页相关元素：
PageIndex – 页面索引计数，计数0为第一页。
PageSize – 每个页面显示大小。
RecordCount – 总记录数。
PageCount – 页数。

对于后两个参数，笔者在存储过程中以输出参数提供。

1．SQL SERVER 2000中的TOP分页
CREATE PROCEDURE [Zhzuo_GetItemsPage]
    @PageIndex INT, /*@PageIndex从计数,0为第一页*/
    @PageSize INT, /*页面大小*/
    @RecordCount INT OUT, /*总记录数*/
    @PageCount INT OUT /*页数*/

/*获取记录数*/
SELECT @RecordCount = COUNT(*) FROM Production.Product
/*计算页面数据*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/*TOP记录数*/
DECLARE @TOPCOUNT INT
SET @TOPCOUNT = @RecordCount - @PageSize * @PageIndex

DECLARE @SQLSTR NVARCHAR(1000)

IF @PageIndex = 0 OR @PageCount <= 1
BEGIN
    SET @SQLSTR =N‘SELECT TOP ‘+STR(@PageSize)+
    ‘ProductID,Name FROM Production.Product ORDER BY ProductID DESC’
END
ELSE
BEGIN
    IF @PageIndex = @PageCount - 1
    BEGIN
       SET @SQLSTR =N‘SELECT * FROM ( SELECT TOP ‘ + STR(@TOPCOUNT) +
       ‘ProductID,Name FROM Production.Product ORDER BY ProductID ASC) T ORDER BY ProductID DESC’
    END
    ELSE
    BEGIN
       SET @SQLSTR =N‘ SELECT TOP ‘+STR(@PageSize)+‘* FROM (SELECT TOP ‘ + STR(@TOPCOUNT) +
       ‘ProductID,Name FROM Production.Product ORDER BY ProductID ASC) T ORDER BY ProductID DESC’
    END
END

/*执行*/
EXEC (@SQLSTR)

以上存储过程对页数进行判断，如果是第一页或最后一页，进行特殊处理。其他情况使用2次TOP翻转。其中排序条件为ProductID倒序。最后通过EXECUTE执行SQL字符串拼串。

2．SQL SERVER 2005中的TOP分页
CREATE PROCEDURE [dbo].[Zhzuo_GetItemsPage2005TOP]
    @PageIndex INT,
    @PageSize INT,
    @RecordCount INT OUT,
    @PageCount INT OUT
AS

/*基于SQL SERVER 2005 */
IF @PageIndex = 0 OR @PageCount <= 1
BEGIN
    SELECT TOP(@PageSize) ProductID,Name FROM Production.Product ORDER BY ProductID DESC
END
ELSE
BEGIN
    IF @PageIndex = @PageCount - 1
    BEGIN
       SELECT * FROM ( SELECT TOP(@TOPCOUNT) ProductID,Name FROM Production.Product ORDER BY ProductID ASC) T
       ORDER BY ProductID DESC
    END
    ELSE
    BEGIN
       SELECT TOP(@PageSize) * FROM (SELECT TOP(@TOPCOUNT) ProductID,Name FROM Production.Product ORDER BY ProductID ASC) T
       ORDER BY ProductID DESC
    END
END

以上存储过程是使用2005的TOP (表达式) 新功能，避免了字符串拼串，使结构化查询语言变得简洁。实现的为同样的功能。

3．SQL SERVER 2005中的新分页
CREATE PROCEDURE [dbo].[Zhzuo_GetItemsPage2005]
    @PageIndex INT,
    @PageSize INT,
    @RecordCount INT OUT,
    @PageCount INT OUT
AS

/*获取记录数*/
SELECT @RecordCount = COUNT(*) FROM Production.Product
/*计算页面数据*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/* 基于SQL SERVER 2005 */
SELECT SerialNumber,ProductID,Name FROM
(SELECT ProductID,Name,ROW_NUMBER() OVER (ORDER BY ProductID DESC) AS SerialNumber FROM Production.Product ) AS T
WHERE T.SerialNumber > (@PageIndex * @PageSize) and T.SerialNumber <= ((@PageIndex+1) * @PageSize)

第三个存储过程使用2005下新的功能，实现的分页存储过程功能更加简单明了，而且更加容易理解。注意这里的ProductID为主键,根据ProductID进行排序生成ROW_NUMBER，通过ROW_NUMBER来确定具体的页数。

通过对三个分页存储过程的比较，可见SQL SERVER 的TSQL 语言对分页功能的支持进步不少。使分页实现趋向于简单化。

补充：

一条SQL语句搞定Sql Server 2000 分页

http://blog.csdn.net/lonz/archive/2004/08/21/80594.aspx

假若你用几十万行数据,你可以看看.

你的存储过程还比不上NOT IN分页,示例:
SELECT Top 10 * FROM Customers WHERE Customerid NOT IN (SELECT TOP 20 Customerid from Customers )

declare @SQLStr varchar(8000)
set @SQLStr=’SELECT Top ‘+cast(@每页大小 as varchar)+’ * FROM 表 WHERE 主键列 NOT IN (SELECT TOP ‘+cast(@每页大小*@第几页 as varchar)+’ 主键列 from 表 )’
exec(@SQLStr)

我个人认为最好的分页方法是:
Select top 10 * from table where id>200
写成存储过程,上面的语句要拼一下sql语句,要获得最后大于的哪一个ID号

那个用游标的方式,只适合于小数据量的表,如果表在一万行以上,就差劲了

你可以在SQL 查询分析器,看一下执行时间.显示执行计划,显示服务器跟踪统计,显示客户统计

以下是我用存储过程分页的方式,分页一个20万行的表,结果显示在网页中,你可以看一下速度:

http://www.bizlands.com/trade/search.asp?type=s&sortid=15

http://www.yibiz.com/trade/search.asp?sortid=5&type=供应&page=3&country=&sf=&cs=&txtitle=&DateTime=

http://www.yibiz.com/trade/search.asp?sortid=7&type=供应

在asp.net中,超过一万行的数据,不建议用Datagrid自带的分页方式,我一般自己写sp

很久前,我说过用sql的游标

游标是存放在内存中,很费内存.

游标一建立，就将相关的记录锁住，直到取消游标

游标提供了对特定集合中逐行扫描的手段，一般使用游标来逐行遍历数据，根据取出数据条件的不同进行不同的操作。而对于多表和大表中定义的游标（大的数据集合）循环很容易使程序进入一个漫长的等待甚至死机.