DECLARE @PK /* PKType */
DECLARE @tblPK
TABLE(
PK /*PKType*/ NOT NULL PRIMARY KEY
)
DECLARE PagingCursor CURSOR DYNAMICREAD_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([OrderDetails].UnitPrice*[ OrderDetails ] .Quantity)
AS [Totalmoneyspent]
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [ OrderDetails ] ON Orders.OrderID = [ OrderDetails].OrderID
WHERE Customers.Country <> 'USA' AND Customers.Country <> 'Mexico '
GROUP BY Customers.ContactName,Customers.Address,Customers.City, Customers.Country
HAVING(SUM([OrderDetails].UnitPrice * [ OrderDetails ] .Quantity)) > 1000
ORDER BY Customer DESC ,Address DESC
返回第二个页面的分页存储调用如下：
EXEC ProcedureName
/*Tables */
'
Customers
INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
INNER JOIN [OrderDetails] ON Orders.OrderID=[OrderDetails].OrderID
'
,
/* PK */
'
Customers.CustomerID
'
,
/* ORDERBY */
'
Customers.ContactName DESC,Customers.AddressDESC
'
,
/*PageNumber */
2
,
/*PageSize */
10
,
/*Fields */
'
Customers.Contact Name AS Customer,
Customers.Address '' , '' Customers.City '' , '' Customers.Country ASAddress, SUM([OrderDetails].UnitPrice*[OrderDetails].Quantity)AS[Totalmoneyspent]
'
,
/*Filter */
'
Customers.Country<>'' USA '' ANDCustomers.Country<> '' Mexico ''' ,
/*GroupBy */
'
Customers.CustomerID,Customers.ContactName,Customers.Address,
Customers.City,Customers.Country
HAVING(SUM([OrderDetails].UnitPrice*[OrderDetails].Quantity))>1000
'
值得注意的是，在原始查询中在ORDER BY语句中使用了别名，但你最好不要在分页存储过程中这么做，因为这样的话跳过开始记录之前的行是很消耗时间的。其实有很多种方法可以用于实现，但原则是不要在一开始把所有的字段包括进去，而仅仅是包括主键列（等同于RowCount方法中的排序列），这样可以加快任务完成速度。只有在请求页中，才获得所有需要的字段。并且，在最终查询中不存在字段别名，在跳行查询中，必须提前使用索引列。
行计数(RowCount)存储过程有一个另外的问题，要实现通用化，在ORDER BY语句中只允许有一个列，这也是升序-降序方法和游标方法的问题，虽然他们可以对几个列进行排序，但是必须保证主键中只有一个字段。我猜如果用更多的动态SQL是可以解决这个问题的，但是在我看来这不是很值得。虽然这样的情况很有可能发生，但他们发生的频率不是很高。通常你可以用上面的原理也独立的分页存储过程。
性能测试
在测试中，我使用了四种方法，如果你有更好的方法的话，我很有兴趣知道。不管如何，我需要对这些方法进行比较，并且评估它们的性能。首先我的第一个想法就是写一个asp.net包含分页DataGrid的测试应用程序，然后测试页面结果。当然，这无法反映存储过程的真实响应时间，所以控制台应用程序显得更加适合。我还加入了一个Web应用程序，但不是为了性能测试，而是一个关于DataGrid自定义分页和存储过程一起工作的例子。
在测试中，我使用了一个自动生成得大数据表，大概插入了500000条数据。如果你没有一张这样的表来做实验，你可以点击这里下载一段用于生成数据的表设计和存储过程脚本。我没有使用一个自增的主键列，而是用一个唯一识别码来识别记录的。如果我使用上面提到的脚本，你可能会考虑在生成表之后添加一个自增列，这些自增数据会根据主键进行数字排序，这也意味着你打算用一个带有主键排序的分页存储过程来获得当前页的数据。
为了实现性能测试，我是通过一个循环多次调用一个特定的存储过程，然后计算平均相应时间来实现的。考虑到缓存的原因，为了更准确地建模实际情况——同一页面对于一个存储过程的多次调用获得数据的时间通常是不适合用来做评估的，因此，我们在调用同一个存储过程时，每一次调用所请求的页码应该是随机的。当然，我们必须假设页的数量是固定的，10-20页，不同页码的数据可能被获取很多次，但是是随机获取的。
有一点我们很容易注意到，相应时间是由要获取的页数据相对于结果集开始的位置的距离决定的，越是远离结果集的开始位置，就有越多的记录要跳过，这也是我为什么不把前20也包括进我的随机序列的原因。作为替换，我会使用2的n次方个页面，循环的大小是需要的不同页的数量*1000，所以，每个页面几乎都被获取了1000次（由于随机原因，肯定会有所偏差）
结果
这里有我的测试结果：
文章整理：西部数码--专业提供域名注册、虚拟主机服务
http://www.west263.com
以上信息与文章正文是不可分割的一部分,如果您要转载本文章,请保留以上信息，谢谢!
