SQL Server 2008 R2中的有效分页 [英] Efficient pagination in SQL Server 2008 R2
问题描述
我想编写一个执行以下操作的存储过程: *返回与查询匹配的记录数(以确定显示给用户的页面数) *从当前页面的查询中返回具有特定记录子集的另一个记录集.
I want to write a stored procedure that does the following: * Returns the number of records matching a query (to determine the number of pages to show the user) * Returns another recordset with a certain subset of records from a query for the current page.
从我在StackOverflow上发现的另一个问题(对不起，我丢失了链接)，我发现我们这样做是分页的:
From another question I found on StackOverflow (sorry, I lost the link), I found that we do pagination like this:
SELECT u.* FROM
(
SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) as rownum FROM
(
[obscenely long select query]
)
as t
)
AS u WHERE u.rowNum >= 1 AND u.rowNum <= 20
我可以通过执行以下操作来获取[冗长的选择查询]返回的记录数:
And I can get the number of records returned by [obscenely long select query] by doing the following:
SELECT COUNT(*) AS numRecords from ([obscenely long select query]) AS records
我想尽可能有效地获得这两个记录集.
I want to get both of these recordsets as effectively as possible.
我想出办法的唯一方法是将以上两个语句都放入一个存储过程中.由于某些原因，这使我感到效率低下:
The only way I can figure how to do it is shoving both of those statements above into one stored procedure. This strikes me as inefficient for a few reasons:
- 我必须两次写出[冗长的选择查询]，使解决方案难以维护/更改(如果可能，我真的希望避免这种情况).
- 我必须运行[过长的选择查询]，获取所有可能的记录(可能是几千条)，然后再将其缩减为合理的分页大小，而仅在获得计数时再次运行.
- I have to write out [obscenely long select query] twice, making the solution harder to maintain / change (I really want to avoid this, if possible).
- I have to run [obscenely long select query], getting all the possible records (which could be thousands) before cutting it down to a reasonable size for the pagination, only to run it again when getting the count.
我尝试过的其他解决方案:
Other solutions I've tried:
- 将[冗长的选择查询]放入其自己的存储过程中(这会导致语法错误).
有什么可以做的吗?我应该只是吸收它并坚持使用当前的解决方案，还是有一种更优雅的方法来实现这一目标?
Is there anything that can be done? Should I just suck it up and stick with my current solution, or is there a more elegant way to pull this off?
推荐答案
根据[obscenely long select query]结构，您可能可以使用临时表或表变量:
-用匹配行的ID填充临时表
-计算临时表的行数以计算页面数
-为调用者检索结果，请建立一个临时表与相关数据库表联接的查询
depending on the [obscenely long select query] structure you may be able to use a temp table or table variable:
- fill a temp table with the ids of the matching rows
- count the temp table rows to calculate the number of pages
- to retrieve the results for the caller build a query with the temp table joined with the relevant database tables
这篇关于SQL Server 2008 R2中的有效分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
