Microsoft SQL Server分页 [英] Microsoft SQL Server Paging
问题描述
关于stackoverflow的许多SQL Server分页问题，其中许多谈论使用ROW_NUMBER() OVER (ORDER BY ...) AND CTE.一旦进入数十万行并开始在非主键值上添加排序并添加自定义WHERE子句，这些方法将变得非常无用.我有一个数百万行的数据集，我正在尝试使用自定义排序和筛选来进行分页，但是即使在排序和筛选所依据的所有字段上都有索引，我的性能也很差.我什至甚至将我的SELECT列包括在每个索引中，但是这几乎没有帮助并且严重地my肿了我的数据库.
There are a number of sql server paging questions on stackoverflow and many of them talk about using ROW_NUMBER() OVER (ORDER BY ...) AND CTE. Once you get into the hundreds of thousands of rows and start adding sorting on non-primary key values and adding custom WHERE clauses, these methods become very inneficient. I have a dataset of several million rows I am trying to page through with custom sorting and filtering, but I am getting poor performance, even with indexes on all the fields that I sort by and filter by. I even went as far as to include my SELECT columns in each of the indexes, but this barely helped and severely bloated my database.
我注意到无论您单击什么排序标准或页码，stackoverflow分页仅花费大约500毫秒.任何人都知道如何使分页在SQL Server 2008中有效地进行分页处理吗?这将包括尽可能高效地获取总行数.
I noticed the stackoverflow paging only takes about 500 milliseconds no matter what sorting criteria or page number you click on. Anyone know how to make paging work efficiently in SQL Server 2008 with millions of rows? This would include getting the total rows as efficiently as possible.
我当前的查询与关于分页的stackoverflow问题具有完全相同的逻辑: 使用SQL Server 2005的最佳分页解决方案?
My current query has the exact same logic as this stackoverflow question about paging: Best paging solution using SQL Server 2005?
推荐答案
任何人都知道如何在具有数百万行的SQL Server 2008中有效地使分页工作吗?
Anyone know how to make paging work efficiently in SQL Server 2008 with millions of rows?
如果您想精确地进行完美的分页，则不能替代为每条记录建立一个索引键(位置行号).但是，还有其他选择.
If you want accurate perfect paging, there is no substitute for building an index key (position row number) for each record. However, there are alternatives.
(1)总页数(记录)
- 假设变化率很小，您可以使用
sysindexes.rows的近似值(几乎是瞬间). - 您可以使用触发器来保持对表行数的精确度，直到第二个
- You can use an approximation from
sysindexes.rows(almost instant) assuming the rate of change is small. - You can use triggers to maintain a completely accurate, to the second, table row count
(2)分页
(a)
您可以在接下来的五页内显示跳转到记录两侧的页面.它们每面最多需要扫描{页面大小} x 5.如果您的基础查询适合快速进行排序，那么这应该不会太慢.因此，给定一条记录X，您可以使用转到上一页(假设排序顺序为a asc, b desc
(a)
You can show page jumps within say the next five pages to either side of a record. These need to scan at most {page size} x 5 on each side. If your underlying query lends itself to travelling along the sort order quickly, this should not be slow. So given a record X, you can go to the previous page using (assuming sort order is a asc, b desc
select top(@pagesize) t.*
from tbl x
inner join tbl t on (t.a = x.a and t.b > x.b) OR
(t.a < a.x)
where x.id = @X
order by t.a asc, t.b desc
(即X之前的记录的最后{页面大小})
(i.e. the last {page size} of records prior to X)
要返回五页，请将其增加到TOP(@ pagesize * 5)，然后再从该子查询中增加TOP(@pagesize).
To go five pages back, you increase it to TOP(@pagesize*5) then further TOP(@pagesize) from that subquery.
缺点:此选项要求您不能直接跳转到特定位置，您的选项仅是FIRST(简单)，LAST(简单)，NEXT/PRIOR，每边＜5页
Downside: This option requires that you cannot directly jump to a particular location, your options are only FIRST (easy), LAST (easy), NEXT/PRIOR, <5 pages either side
(b)
如果分页总是非常明确和可预测的，则维护一个索引视图或触发器更新的表，该表的行号中不包含空格.如果这些表通常只在频谱的一端看到更新，而删除的空白很容易通过移动不太多的记录来快速填充，则这可能是一种选择.
(b)
If the paging is always going to be quite specific and predictable, maintain an INDEXED view or trigger-updated table that does not contain gaps in the row number. This may be an option if the tables normally only see updates at one end of the spectrum, with gaps from deletes easily filled quickly by shifting not-so-many records.
这种方法为您提供了行数(最后一行)，还可以直接访问任何页面.
This approach gives you a rowcount (last row) and also direct access to any page.
这篇关于Microsoft SQL Server分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
