使用Row_Number在Sql Server 2008中实现表分页是否存在任何性能问题? [英] Is there any performance issue using Row_Number to implement table paging in Sql Server 2008?

问题描述

我想使用此方法实现表分页:

I want to implement table paging using this method:

SET @PageNum = 2;
SET @PageSize = 10;

WITH OrdersRN AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
          ,*
      FROM dbo.Orders
)

SELECT * 
  FROM OrdersRN
 WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 
                  AND @PageNum * @PageSize
 ORDER BY OrderDate ,OrderID;

我应该注意什么吗? 表有数百万条记录.

Is there anything I should be aware of ? Table has millions of records.

谢谢.

在使用建议的MAXROWS方法一段时间后(它确实非常快地工作了)，由于其更大的灵活性，我不得不切换回ROW_NUMBER方法.到目前为止，我对它的速度也感到非常满意(我正在与View合作，拥有10列以上的1M记录).要使用任何类型的查询，我都需要进行以下修改:

After using suggested MAXROWS method for some time (which works really really fast) I had to switch back to ROW_NUMBER method because of its greater flexibility. I am also very happy about its speed so far (I am working with View having more then 1M records with 10 columns). To use any kind of query I use following modification:

PROCEDURE [dbo].[PageSelect] 
(
  @Sql nvarchar(512),
  @OrderBy nvarchar(128) = 'Id',
  @PageNum int = 1,
  @PageSize int = 0    
)
AS
BEGIN
SET NOCOUNT ON

 Declare @tsql as nvarchar(1024)
 Declare @i int, @j int

 if (@PageSize <= 0) OR (@PageSize > 10000)
  SET @PageSize = 10000  -- never return more then 10K records

 SET @i = (@PageNum - 1) * @PageSize + 1 
 SET @j = @PageNum * @PageSize

 SET @tsql = 
 'WITH MyTableOrViewRN AS
 (
  SELECT ROW_NUMBER() OVER(ORDER BY ' + @OrderBy + ') AS RowNum
     ,*
    FROM MyTableOrView
    WHERE ' + @Sql  + '

 )
 SELECT * 
  FROM MyTableOrViewRN 
  WHERE RowNum BETWEEN ' + CAST(@i as varchar) + ' AND ' + cast(@j as varchar)

 exec(@tsql)
END

如果使用此过程，请确保您阻止了sql注入.

If you use this procedure make sure u prevented sql injection.

推荐答案

我实际上已经写过几次. ROW_NUMBER是迄今为止最灵活和易于使用的，并且性能好，但是对于非常大的数据集，它并不总是 best . SQL Server仍然需要对数据进行排序，并且排序可能会变得非常昂贵.

I've written about this a few times actually; ROW_NUMBER is by far the most flexible and easy-to-use, and performance is good, but for extremely large data sets it is not always the best. SQL Server still needs to sort the data and the sort can get pretty expensive.

这里有一个不同的方法，该方法使用了几个变量和并且只要您有正确的索引，它就非常快.它很旧，但是据我所知，它仍然是最有效的.基本上，您可以使用SET ROWCOUNT做一个完全幼稚的SELECT，并且SQL Server可以优化大部分实际工作.计划和成本最终类似于两个MAX/MIN查询，通常比单个窗口查询要快得多.对于非常大的数据集，运行时间不到总时间的1/10.

There's a different approach here that uses a couple of variables and SET ROWCOUNT and is extremely fast, provided that you have the right indexes. It's old, but as far as I know, it's still the most efficient. Basically you can do a totally naïve SELECT with SET ROWCOUNT and SQL Server is able to optimize away most of the real work; the plan and cost ends up being similar to two MAX/MIN queries, which is usually a great deal faster than even a single windowing query. For very large data sets this runs in less than 1/10th the time.

话虽如此，当人们问起如何实现分页或逐组最大值之类的事情时，我仍然总是建议ROW_NUMBER，因为它很容易使用.如果您开始注意到ROW_NUMBER的运行速度变慢，我只会开始研究上述类似的替代方案.

Having said that, I still always recommend ROW_NUMBER when people ask about how to implement things like paging or groupwise maximums, because of how easy it is to use. I would only start looking at alternatives like the above if you start to notice slowdowns with ROW_NUMBER.

这篇关于使用Row_Number在Sql Server 2008中实现表分页是否存在任何性能问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！