在SQL Server 2005中，通过很大的结果集进行分页的有效方法是什么? [英] What is an efficient method of paging through very large result sets in SQL Server 2005?

问题描述

编辑:我仍在等待更多答案.谢谢！

在SQL 2000时代，我曾经使用临时表方法，在该方法中，使用新的标识列和主键创建一个临时表，然后选择A和B之间的标识列.

In SQL 2000 days, I used to use temp table method where you create a temp table with new identity column and primary key then select where identity column between A and B.

当 SQL 2005 出现时，我发现了有关Row_Number()的信息，从那时起我就一直在使用它...

When SQL 2005 came along I found out about Row_Number() and I've been using it ever since...

但是现在，我发现Row_Number()存在严重的性能问题. 当您处理不太庞大的结果集并通过标识列进行排序时，它的性能很好.但是，当您处理大型结果集(例如10,000多个记录，并按非身份列对它们进行排序)时，它的效果非常差.即使结果集超过250,000条记录，即使您按标识列进行排序，Row_Number()的性能也很差.对我来说，它到了一个错误，即"命令超时！"

But now, I found a serious performance issue with Row_Number(). It performs very well when you are working with not-so-gigantic result sets and sorting over an identity column. However, it performs very poorly when you are working with large result sets like over 10,000 records and sorting it over non-identity column. Row_Number() performs poorly even if you sort by an identity column if the result set is over 250,000 records. For me, it came to a point where it throws an error, "command timeout!"

如何使用分页对SQL 2005上的大型结果集进行分页? 在这种情况下，临时表方法仍然更好吗?我不确定将临时表与SET ROWCOUNT一起使用是否可以执行更好...但是有些人说，如果您有多列主键，则存在行号输入错误的问题.

What do you use to do paginate a large result set on SQL 2005? Is temp table method still better in this case? I'm not sure if this method using temp table with SET ROWCOUNT will perform better... But some say there is an issue of giving wrong row number if you have multi-column primary key.

对于我来说，我需要能够按日期类型列为我的生产Web应用排序结果集.

In my case, I need to be able to sort the result set by a date type column... for my production web app.

让我知道您在SQL 2005中用于高性能分页的用途.而且我还想知道一种创建索引的聪明方法. 我怀疑选择正确的主键和/或索引(群集/非群集)将在这里发挥重要作用.

Let me know what you use for high-performing pagination in SQL 2005. And I'd also like to know a smart way of creating indexes. I'm suspecting choosing right primary keys and/or indexes (clustered/non-clustered) will play a big role here.

谢谢.

P.S. 有人知道堆栈溢出使用什么吗?

编辑:我的头像看起来像...

Mine looks something like...

SELECT postID, postTitle, postDate
FROM
   (SELECT postID, postTitle, postDate, 
         ROW_NUMBER() OVER(ORDER BY postDate DESC, postID DESC) as RowNum
    FROM MyTable
   ) as DerivedMyTable
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1

postID:整数，身份(自动递增)，主键

postID: Int, Identity (auto-increment), Primary key

postDate:DateTime

postDate: DateTime

编辑:每个人都在使用Row_Number()吗?

Is everyone using Row_Number()?

推荐答案

如果您在PostDate字段中有索引，那么对于示例查询ROW_COUNT来说，它的行数应该非常快，包含数千行.如果不这样做，则服务器需要在PK上执行完整的聚集索引扫描，实际上是加载每个页面，获取PostDate字段，对其进行排序，确定要为结果集提取的行，然后再次获取这些行.这是一遍又一遍地创建临时索引的过程(您可能会在平原上看到一个表/索引假脱机).

Well, for your sample query ROW_COUNT should be pretty fast with thousands of rows, provided you have an index on your PostDate field. If you don't, the server needs to perform a complete clustered index scan on your PK, practically load every page, fetch your PostDate field, sort by it, determine the rows to extract for the result set and again fetch those rows. It's kind of creating a temp index over and over again (you might see an table/index spool in the plain).

难怪你会超时.

我的建议:在PostDate DESC上设置一个索引，这就是ROW_NUMBER将要遍历的内容-(ORDER BY PostDate DESC，...)

My suggestion: set an index on PostDate DESC, this is what ROW_NUMBER will go over - (ORDER BY PostDate DESC, ...)

关于您所引用的文章-过去，我在没有ROW_COUNT的情况下使用SQL Server 2000完成了很多分页和工作，并且本文中使用的方法是最有效的方法.并非在所有情况下都有效(您需要唯一或几乎唯一的值). 此处.

As for the article you are referring to - I've done pretty much paging and stuff with SQL Server 2000 in the past without ROW_COUNT and the approach used in the article is the most efficient one. It does not work in all circumstances (you need unique or almost unique values). An overview of some other methods is here.

.

这篇关于在SQL Server 2005中，通过很大的结果集进行分页的有效方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！