代码之家 › 专栏 › 技术社区 › majkinetor

在SQLServer2008中使用Row_Number实现表分页是否存在性能问题?

paging sql-server

majkinetor · 技术社区 · 16 年前

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

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;

有什么我应该知道的吗? 表中有数百万条记录。

编辑: MAXROWS 方法有一段时间(它的工作速度非常快),我不得不切换回 ROW_NUMBER

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

3 回复 | 直到 16 年前

Aaronaught 16 年前

事实上,我已经写过好几次了; ROW_NUMBER 好的 ,但对于非常大的数据集,它并不总是 最好的 . SQL Server仍然需要对数据进行排序,而这种排序可能会非常昂贵。

有一个 different approach here SET ROWCOUNT 而且速度非常快,只要你有正确的索引。它很旧,但据我所知,它仍然是最有效的。基本上你可以做一个中堂 SELECT SQL Server能够优化大部分实际工作;计划和成本最终类似于两个 MAX MIN 查询,通常比单个窗口查询快得多。对于非常大的数据集,运行时间不到十分之一。

话虽如此,我还是一直建议 行号 行号 .

Jim G. 11 年前

最近,我在具有星型模式的数据仓库环境中使用了分页。当我将CTE限制为只查询确定所需的行时,我发现性能非常好 ROW_NUMBER . 我让CTE退回了 行号

在主查询中,我引用了 行号 用于分页,然后基于来自CTE的其他主键连接到其他表。我发现连接只在满足条件的行上执行 WHERE 子句,节省了大量时间。

-2

masoud ramezani 16 年前

测试这个解决方案,也许它更好。请根据你的需要换这个。

CREATE PROCEDURE sp_PagedItems
    (
     @Page int,
     @RecsPerPage int
    )
AS

-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON


--Create a temporary table
CREATE TABLE #TempItems
(
    ID int IDENTITY,
    Name varchar(50),
    Price currency
)


-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (Name, Price)
SELECT Name,Price FROM tblItem ORDER BY Price

-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
       MoreRecords =
    (
     SELECT COUNT(*)
     FROM #TempItems TI
     WHERE TI.ID >= @LastRec
    )
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec


-- Turn NOCOUNT back OFF
SET NOCOUNT OFF