[数据库] 通用分页存储过程第1/5页
答
CREATE TABLE #Temp (
ID int IDENTITY PRIMARY KEY,
PK /* here goes PK type */
)
INSERT INTO #Temp SELECT PK FROM Table ORDER BY SortColumn
SELECT FROM Table JOIN #Temp temp ON Table.PK = temp.PK ORDER BY temp.ID
WHERE ID > @StartRow AND ID < @EndRow
通过把所有的行拷贝到临时表中，我们可以对查询进一步的优化(SELECT TOP EndRow …)，但是关键在于最坏情况——一个包含100万记录的表就会产生一个100万条记录的临时表。考虑到这样的情况，再看看上面文章的结果，我决定在我的测试中放弃该方法
临时表
首先，我准备先来说一下临时表方法，这是一个广泛被建议使用的解决方案，我在项目中遇到过好几次了，这里有另一篇解释它如何工作的文章，还有一个如何在DataGrid中是用定制化分页（Custom Paging）的例子：
ASP.NET DataGrid分页 第二部分 – 定制化分页
这两篇文章中的方法都是通过把主键数据拷贝到临时表中，然后对主查询做join实现查询优化。下面让我们来看看这个方法的实质：
复制代码 代码如下:
CREATE TABLE #Temp (
ID int IDENTITY PRIMARY KEY,
PK /* here goes PK type */
)
INSERT INTO #Temp SELECT PK FROM Table ORDER BY SortColumn
SELECT FROM Table JOIN #Temp temp ON Table.PK = temp.PK ORDER BY temp.ID
WHERE ID > @StartRow AND ID < @EndRow
通过把所有的行拷贝到临时表中，我们可以对查询进一步的优化(SELECT TOP EndRow …)，但是关键在于最坏情况——一个包含100万记录的表就会产生一个100万条记录的临时表。考虑到这样的情况，再看看上面文章的结果，我决定在我的测试中放弃该方法
答
DECLARE @temp TABLE (
PK /* PK Type */ NOT NULL PRIMARY
)
INSERT INTO @temp
SELECT TOP @PageSize PK FROM (
SELECT TOP (@StartRow + @PageSize)
PK,
SortColumn /*If sorting column is defferent from the PK, SortColumn must
be fetched as well, otherwise just the PK is necessary */
ORDER BY SortColumn /* default order – typically ASC */)
ORDER BY SortColumn /* reversed default order – typically DESC */
SELECT FROM Table JOIN @Temp temp ON Table.PK = temp.PK
ORDER BY SortColumn /* default order */
行计数
这个方法的基本逻辑依赖于SQL中的SET ROWCOUNT表达式，这样可以跳过不必要的行并且获得需要的行记录
DECLARE @Sort /* the type of the sorting column */
SET ROWCOUNT @StartRow
SELECT @Sort = SortColumn FROM Table ORDER BY SortColumn
SET ROWCOUNT @PageSize
SELECT FROM Table WHERE SortColumn >= @Sort ORDER BY SortColumn
子查询
还有两个方法也是我考虑过的，他们的来源不同。第一个是众所周知的三角查询(Triple Query)或者说自查询方法，我找的一个比较透彻的方法在下面的文章中有描述
SQL Server服务器端分页
虽然你需要订阅，但是可以下载一个包含子查询存储过程定义的zip文件。列表4 SELECT_WITH_PAGINGStoredProcedure.txt文件包含一个完整的通用的动态SQL。在本文中，我也用一个类似的包含所有其他存储过程的通用逻辑。这里的原理是连接到整个过程中，我对原始代码做了一些缩减，因为recordcount在我的测试中不需要）
SELECT FROM Table WHERE PK IN
(SELECT TOP @PageSize PK FROM Table WHERE PK NOT IN
(SELECT TOP @StartRow PK FROM Table ORDER BY SortColumn)
ORDER BY SortColumn)
ORDER BY SortColumn
游标
在看google讨论组的时候，我找到了最后一个方法，你可以点这里查看原始帖子。该方法是用了一个服务器端动态游标。许多人试图避免使用游标，因为游标没有关系可言，以及有序性导致其效率不高，但回过头来看，分页其实是一个有序的任务，无论你使用哪种方法，你都必须回到开始行记录。在之前的方法中，先选择所有在开始记录之前的所有行，加上需要的行记录，然后删除所有之前的行。动态游标有一个FETCH RELATIVE选项可以完成魔法般的跳转。基本的逻辑如下：
DECLARE @PK /* PK Type */
DECLARE @tblPK TABLE (
PK /* PK Type */ NOT NULL PRIMARY KEY
)
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT @PK FROM Table ORDER BY SortColumn
OPEN PagingCursor
FETCH RELATIVE @StartRow FROM PagingCursor INTO @PK
WHILE @PageSize > 0 AND @@FETCH_STATUS = 0
BEGIN
INSERT @tblPK(PK) VALUES(@PK)
FETCH NEXT FROM PagingCursor INTO @PK
SET @PageSize = @PageSize - 1
END
CLOSE PagingCursor
DEALLOCATE PagingCursor
SELECT FROM Table JOIN @tblPK temp ON Table.PK = temp.PK
ORDER BY SortColumn
复杂查询的通用化
我在之前指出，所有的存储过程都是用动态SQL实现通用性的，因此，理论上它们可以用任何种类的复杂查询。下面有一个基于Northwind数据库的复杂查询例子。
SELECT Customers.ContactName AS Customer,
Customers.Address + ', ' + Customers.City + ', ' +
Customers.Country AS Address,
SUM([Order Details].UnitPrice*[Order Details].Quantity) AS
[Total money spent]
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE Customers.Country <> 'USA' AND Customers.Country <> 'Mexico'
GROUP BY Customers.ContactName, Customers.Address, Customers.City,
Customers.Country
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
ORDER BY Customer DESC, Address DESC
返回第二个页面的分页存储调用如下：
EXEC ProcedureName
/* Tables */
'Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID',
/* PK */
'Customers.CustomerID',
/* ORDER BY */
'Customers.ContactName DESC, Customers.Address DESC',
/* PageNumber */
2,
/* Page Size */
10,
/* Fields */
'Customers.ContactName AS Customer,
Customers.Address + '', '' + Customers.City + '', '' + Customers.Country
AS Address,
SUM([Order Details].UnitPrice*[Order Details].Quantity) AS [Total money spent]',
/* Filter */
'Customers.Country <> ''USA'' AND Customers.Country <> ''Mexico''',
/*Group By*/
'Customers.CustomerID, Customers.ContactName, Customers.Address,
Customers.City, Customers.Country
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000'
值得注意的是，在原始查询中在ORDER BY语句中使用了别名，但你最好不要在分页存储过程中这么做，因为这样的话跳过开始记录之前的行是很消耗时间的。其实有很多种方法可以用于实现，但原则是不要在一开始把所有的字段包括进去，而仅仅是包括主键列（等同于RowCount方法中的排序列），这样可以加快任务完成速度。只有在请求页中，才获得所有需要的字段。并且，在最终查询中不存在字段别名，在跳行查询中，必须提前使用索引列。
行计数(RowCount)存储过程有一个另外的问题，要实现通用化，在ORDER BY语句中只允许有一个列，这也是升序-降序方法和游标方法的问题，虽然他们可以对几个列进行排序，但是必须保证主键中只有一个字段。我猜如果用更多的动态SQL是可以解决这个问题的，但是在我看来这不是很值得。虽然这样的情况很有可能发生，但他们发生的频率不是很高。通常你可以用上面的原理也独立的分页存储过程。
性能测试
在测试中，我使用了四种方法，如果你有更好的方法的话，我很有兴趣知道。不管如何，我需要对这些方法进行比较，并且评估它们的性能。首先我的第一个想法就是写一个asp.net包含分页DataGrid的测试应用程序，然后测试页面结果。当然，这无法反映存储过程的真实响应时间，所以控制台应用程序显得更加适合。我还加入了一个Web应用程序，但不是为了性能测试，而是一个关于DataGrid自定义分页和存储过程一起工作的例子。这两个应用程序都可以在 Paging Test Solution中找到。
在测试中，我使用了一个自动生成得大数据表，大概插入了500000条数据。如果你没有一张这样的表来做实验，你可以点击这里下载一段用于生成数据的表设计和存储过程脚本。我没有使用一个自增的主键列，而是用一个唯一识别码来识别记录的。如果我使用上面提到的脚本，你可能会考虑在生成表之后添加一个自增列，这些自增数据会根据主键进行数字排序，这也意味着你打算用一个带有主键排序的分页存储过程来获得当前页的数据。
为了实现性能测试，我是通过一个循环多次调用一个特定的存储过程，然后计算平均相应时间来实现的。考虑到缓存的原因，为了更准确地建模实际情况——同一页面对于一个存储过程的多次调用获得数据的时间通常是不适合用来做评估的，因此，我们在调用同一个存储过程时，每一次调用所请求的页码应该是随机的。当然，我们必须假设页的数量是固定的，10-20页，不同页码的数据可能被获取很多次，但是是随机获取的。
有一点我们很容易注意到，相应时间是由要获取的页数据相对于结果集开始的位置的距离决定的，越是远离结果集的开始位置，就有越多的记录要跳过，这也是我为什么不把前20也包括进我的随机序列的原因。作为替换，我会使用2的n次方个页面，循环的大小是需要的不同页的数量*1000，所以，每个页面几乎都被获取了1000次（由于随机原因，肯定会有所偏差）
升序-降序
这个方法在子查询中使用默认排序，在主查询中使用反向排序，原理是这样的：
复制代码 代码如下:
DECLARE @temp TABLE (
PK /* PK Type */ NOT NULL PRIMARY
)
INSERT INTO @temp
SELECT TOP @PageSize PK FROM (
SELECT TOP (@StartRow + @PageSize)
PK,
SortColumn /*If sorting column is defferent from the PK, SortColumn must
be fetched as well, otherwise just the PK is necessary */
ORDER BY SortColumn /* default order – typically ASC */)
ORDER BY SortColumn /* reversed default order – typically DESC */
SELECT FROM Table JOIN @Temp temp ON Table.PK = temp.PK
ORDER BY SortColumn /* default order */
行计数
这个方法的基本逻辑依赖于SQL中的SET ROWCOUNT表达式，这样可以跳过不必要的行并且获得需要的行记录
复制代码 代码如下:
DECLARE @Sort /* the type of the sorting column */
SET ROWCOUNT @StartRow
SELECT @Sort = SortColumn FROM Table ORDER BY SortColumn
SET ROWCOUNT @PageSize
SELECT FROM Table WHERE SortColumn >= @Sort ORDER BY SortColumn
子查询
还有两个方法也是我考虑过的，他们的来源不同。第一个是众所周知的三角查询(Triple Query)或者说自查询方法，我找的一个比较透彻的方法在下面的文章中有描述
SQL Server服务器端分页
虽然你需要订阅，但是可以下载一个包含子查询存储过程定义的zip文件。列表4 SELECT_WITH_PAGINGStoredProcedure.txt文件包含一个完整的通用的动态SQL。在本文中，我也用一个类似的包含所有其他存储过程的通用逻辑。这里的原理是连接到整个过程中，我对原始代码做了一些缩减，因为recordcount在我的测试中不需要）
复制代码 代码如下:
SELECT FROM Table WHERE PK IN
(SELECT TOP @PageSize PK FROM Table WHERE PK NOT IN
(SELECT TOP @StartRow PK FROM Table ORDER BY SortColumn)
ORDER BY SortColumn)
ORDER BY SortColumn
游标
在看google讨论组的时候，我找到了最后一个方法，你可以点这里查看原始帖子。该方法是用了一个服务器端动态游标。许多人试图避免使用游标，因为游标没有关系可言，以及有序性导致其效率不高，但回过头来看，分页其实是一个有序的任务，无论你使用哪种方法，你都必须回到开始行记录。在之前的方法中，先选择所有在开始记录之前的所有行，加上需要的行记录，然后删除所有之前的行。动态游标有一个FETCH RELATIVE选项可以完成魔法般的跳转。基本的逻辑如下：
复制代码 代码如下:
DECLARE @PK /* PK Type */
DECLARE @tblPK TABLE (
PK /* PK Type */ NOT NULL PRIMARY KEY
)
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT @PK FROM Table ORDER BY SortColumn
OPEN PagingCursor
FETCH RELATIVE @StartRow FROM PagingCursor INTO @PK
WHILE @PageSize > 0 AND @@FETCH_STATUS = 0
BEGIN
INSERT @tblPK(PK) VALUES(@PK)
FETCH NEXT FROM PagingCursor INTO @PK
SET @PageSize = @PageSize - 1
END
CLOSE PagingCursor
DEALLOCATE PagingCursor
SELECT FROM Table JOIN @tblPK temp ON Table.PK = temp.PK
ORDER BY SortColumn
复杂查询的通用化
我在之前指出，所有的存储过程都是用动态SQL实现通用性的，因此，理论上它们可以用任何种类的复杂查询。下面有一个基于Northwind数据库的复杂查询例子。
复制代码 代码如下:
SELECT Customers.ContactName AS Customer,
Customers.Address + ', ' + Customers.City + ', ' +
Customers.Country AS Address,
SUM([Order Details].UnitPrice*[Order Details].Quantity) AS
[Total money spent]
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE Customers.Country <> 'USA' AND Customers.Country <> 'Mexico'
GROUP BY Customers.ContactName, Customers.Address, Customers.City,
Customers.Country
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
ORDER BY Customer DESC, Address DESC
返回第二个页面的分页存储调用如下：
EXEC ProcedureName
/* Tables */
'Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID',
/* PK */
'Customers.CustomerID',
/* ORDER BY */
'Customers.ContactName DESC, Customers.Address DESC',
/* PageNumber */
2,
/* Page Size */
10,
/* Fields */
'Customers.ContactName AS Customer,
Customers.Address + '', '' + Customers.City + '', '' + Customers.Country
AS Address,
SUM([Order Details].UnitPrice*[Order Details].Quantity) AS [Total money spent]',
/* Filter */
'Customers.Country <> ''USA'' AND Customers.Country <> ''Mexico''',
/*Group By*/
'Customers.CustomerID, Customers.ContactName, Customers.Address,
Customers.City, Customers.Country
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000'
值得注意的是，在原始查询中在ORDER BY语句中使用了别名，但你最好不要在分页存储过程中这么做，因为这样的话跳过开始记录之前的行是很消耗时间的。其实有很多种方法可以用于实现，但原则是不要在一开始把所有的字段包括进去，而仅仅是包括主键列（等同于RowCount方法中的排序列），这样可以加快任务完成速度。只有在请求页中，才获得所有需要的字段。并且，在最终查询中不存在字段别名，在跳行查询中，必须提前使用索引列。
行计数(RowCount)存储过程有一个另外的问题，要实现通用化，在ORDER BY语句中只允许有一个列，这也是升序-降序方法和游标方法的问题，虽然他们可以对几个列进行排序，但是必须保证主键中只有一个字段。我猜如果用更多的动态SQL是可以解决这个问题的，但是在我看来这不是很值得。虽然这样的情况很有可能发生，但他们发生的频率不是很高。通常你可以用上面的原理也独立的分页存储过程。
性能测试
在测试中，我使用了四种方法，如果你有更好的方法的话，我很有兴趣知道。不管如何，我需要对这些方法进行比较，并且评估它们的性能。首先我的第一个想法就是写一个asp.net包含分页DataGrid的测试应用程序，然后测试页面结果。当然，这无法反映存储过程的真实响应时间，所以控制台应用程序显得更加适合。我还加入了一个Web应用程序，但不是为了性能测试，而是一个关于DataGrid自定义分页和存储过程一起工作的例子。这两个应用程序都可以在 Paging Test Solution中找到。
在测试中，我使用了一个自动生成得大数据表，大概插入了500000条数据。如果你没有一张这样的表来做实验，你可以点击这里下载一段用于生成数据的表设计和存储过程脚本。我没有使用一个自增的主键列，而是用一个唯一识别码来识别记录的。如果我使用上面提到的脚本，你可能会考虑在生成表之后添加一个自增列，这些自增数据会根据主键进行数字排序，这也意味着你打算用一个带有主键排序的分页存储过程来获得当前页的数据。
为了实现性能测试，我是通过一个循环多次调用一个特定的存储过程，然后计算平均相应时间来实现的。考虑到缓存的原因，为了更准确地建模实际情况——同一页面对于一个存储过程的多次调用获得数据的时间通常是不适合用来做评估的，因此，我们在调用同一个存储过程时，每一次调用所请求的页码应该是随机的。当然，我们必须假设页的数量是固定的，10-20页，不同页码的数据可能被获取很多次，但是是随机获取的。
有一点我们很容易注意到，相应时间是由要获取的页数据相对于结果集开始的位置的距离决定的，越是远离结果集的开始位置，就有越多的记录要跳过，这也是我为什么不把前20也包括进我的随机序列的原因。作为替换，我会使用2的n次方个页面，循环的大小是需要的不同页的数量*1000，所以，每个页面几乎都被获取了1000次（由于随机原因，肯定会有所偏差）
答
Create Proc proc_PageRecordset
@queryStr nvarchar(2000), --查询语句，用来获取要显示的数据必须满足的要求@keyField nvarchar (72), --主键
@pageSize int, --每页的行数@pageNumber int, --第几页
@filter varChar(2000)='', --过滤，where后头的语句
@order varChar(200)='' --排序方式AS
BEGIN
DECLARE @sqlText AS nvarchar(4000)
DECLARE @sqlTable AS nvarchar(4000)
DECLARE @sqlText_PageCount AS nvarchar(4000)
set @filter=replace(@filter,'#','''')
--这是最后一次所发现的bug，因为在排序中必须确定一个排出来的位置一样的table，不能让a在这次排第2，那次又排第5
if CharIndex(@keyField,@order)=0
Begin
Set @order=@order+','+@keyField
End
if (Rtrim(@filter)='')
begin
SET @sqlTable = 'SELECT TOP ' + CAST((@pageNumber + 1) * @pageSize AS varchar(30)) + ' ' + @queryStr + ' ' + @order
SET @sqlText_PageCount = 'Select Count(*) from (select ' +@queryStr+ ' ) as Table_temp'
end
else
begin
SET @sqlTable = 'SELECT TOP ' + CAST((@pageNumber + 1) * @pageSize AS varchar(30)) + ' ' + @queryStr + ' and ' + @filter + @order
SET @sqlText_PageCount = 'Select Count(*) AS MyCount from (select ' +@queryStr+ ' and ' + @filter +' ) as Table_temp'
end
--譬如现在要第5页数据，每页是16行，那么现在要的数据就是在前80行，并且不在原来的64行内的
SET @sqlText =
'SELECT TOP ' + CAST(@pageSize AS varchar(30)) + ' * ' +
'FROM (' + @sqlTable + ') AS tableA ' +
'WHERE ' + @keyField + ' NOT IN(SELECT TOP ' +
CAST(@pageNumber * @pageSize AS varchar(30)) + ' ' + @keyField +
' FROM (' + @sqlTable + ') AS tableB)'
EXEC (@sqlText)
exec (@sqlText_PageCount)
END
GO
以下这个是我自己写的一个分页通用存储过程
该方法也就是上头所提到的行计数的方式，在系统中使用发现过很多次问题，这是最终版本应该不会再有什么算法之类的问题出现了
这个存储过程的原理就是“譬如现在要第5页数据，每页是16行，那么现在要的数据就是在前80行，并且不在原来的64行内的”
复制代码 代码如下:
Create Proc proc_PageRecordset
@queryStr nvarchar(2000), --查询语句，用来获取要显示的数据必须满足的要求@keyField nvarchar (72), --主键
@pageSize int, --每页的行数@pageNumber int, --第几页
@filter varChar(2000)='', --过滤，where后头的语句
@order varChar(200)='' --排序方式AS
BEGIN
DECLARE @sqlText AS nvarchar(4000)
DECLARE @sqlTable AS nvarchar(4000)
DECLARE @sqlText_PageCount AS nvarchar(4000)
set @filter=replace(@filter,'#','''')
--这是最后一次所发现的bug，因为在排序中必须确定一个排出来的位置一样的table，不能让a在这次排第2，那次又排第5
if CharIndex(@keyField,@order)=0
Begin
Set @order=@order+','+@keyField
End
if (Rtrim(@filter)='')
begin
SET @sqlTable = 'SELECT TOP ' + CAST((@pageNumber + 1) * @pageSize AS varchar(30)) + ' ' + @queryStr + ' ' + @order
SET @sqlText_PageCount = 'Select Count(*) from (select ' +@queryStr+ ' ) as Table_temp'
end
else
begin
SET @sqlTable = 'SELECT TOP ' + CAST((@pageNumber + 1) * @pageSize AS varchar(30)) + ' ' + @queryStr + ' and ' + @filter + @order
SET @sqlText_PageCount = 'Select Count(*) AS MyCount from (select ' +@queryStr+ ' and ' + @filter +' ) as Table_temp'
end
--譬如现在要第5页数据，每页是16行，那么现在要的数据就是在前80行，并且不在原来的64行内的
SET @sqlText =
'SELECT TOP ' + CAST(@pageSize AS varchar(30)) + ' * ' +
'FROM (' + @sqlTable + ') AS tableA ' +
'WHERE ' + @keyField + ' NOT IN(SELECT TOP ' +
CAST(@pageNumber * @pageSize AS varchar(30)) + ' ' + @keyField +
' FROM (' + @sqlTable + ') AS tableB)'
EXEC (@sqlText)
exec (@sqlText_PageCount)
END
GO