Есть ли проблема с производительностью с использованием Row_Number для реализации подкачки таблиц в Sql Server 2008?

Я хочу реализовать пейджинг таблиц с помощью этого метода:

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;

Есть ли что-нибудь, о чем я должен знать? Таблица содержит миллионы записей.

спасибо.

EDIT: После использования предложенного метода MAXROWS в течение некоторого времени (который работает действительно очень быстро) мне пришлось вернуться к методу ROW_NUMBER из-за его большей гибкости. Я также очень доволен своей скоростью (я работаю с View, имея более 1M записей с 10 столбцами). Чтобы использовать любой запрос, я использую следующую модификацию:

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

Если вы используете эту процедуру, убедитесь, что u не удалось выполнить SQL-инъекцию.

Ответы

Ответ 1

Я написал об этом несколько раз на самом деле; ROW_NUMBER на сегодняшний день является самым гибким и простым в использовании, а производительность хороша, но для чрезвычайно больших наборов данных это не всегда лучше. SQL Server по-прежнему необходимо сортировать данные, и сортировка может стать довольно дорогостоящей.

Здесь существует другой подход, который использует пару переменных и SET ROWCOUNT и очень быстро, при условии, что у вас есть нужные индексы. Он старый, но, насколько я знаю, он по-прежнему наиболее эффективен. В принципе, вы можете сделать абсолютно наивный SELECT с SET ROWCOUNT, а SQL Server способен оптимизировать большую часть реальной работы; план и стоимость заканчиваются похожими на два запроса MAX/MIN, что, как правило, намного быстрее, чем даже один запрос на окно. Для очень больших наборов данных это выполняется менее чем за 1/10 времени.

Сказав это, я все еще всегда рекомендую ROW_NUMBER, когда люди спрашивают о том, как реализовать такие вещи, как пейджинговые или групповые максимумы, из-за того, насколько легко использовать. Я бы начал искать альтернативы, подобные приведенным выше, если вы начнете замечать замедление с помощью ROW_NUMBER.

Ответ 2

В последнее время я использовал пейджинг в среде хранилища данных со звездообразной схемой. Я обнаружил, что производительность была очень хорошей, когда я ограничил CTE только запросом строк, необходимых для определения ROW_NUMBER. Я получил CTE ROW_NUMBER плюс первичные ключи других строк, которые помогли определить номер строки.

В основном запросе я обратился к ROW_NUMBER для подкачки, а затем присоединился к другим таблицам на основе других первичных ключей от CTE. Я обнаружил, что соединения выполнялись только в строках, удовлетворяющих предложению WHERE во внешнем запросе, сохраняя много времени.

Ответ 3

проверить это решение, может быть, это лучше. измените это с вашей просьбой.

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