Início / dba / Perguntas / 32884
Accepted
LCJ
Asked: 2013-01-15 06:18:11 +0800 CST

Como a paginação funciona com ROW_NUMBER no SQL Server?

  • 772

Eu tenho uma Employeetabela que tem um milhão de registros. Eu tenho o seguinte SQL para paginar dados em um aplicativo da web. Está funcionando bem. No entanto, o que vejo como um problema é - a tabela derivada tblEmployeeseleciona todos os registros na Employeetabela (para criar os MyRowNumbervalores).

Eu acho que isso causa a seleção de todos os registros na Employeetabela.

Será que realmente funciona assim? Ou o SQL Server é otimizado para selecionar apenas os 5 registros da Employeetabela original também?

DECLARE @Index INT;
DECLARE @PageSize INT;

SET @Index = 3;
SET @PageSize = 5;

SELECT *  FROM
  (SELECT  ROW_NUMBER() OVER (ORDER BY EmpID asc) as MyRowNumber,*
  FROM Employee) tblEmployee
WHERE MyRowNumber BETWEEN ( ((@Index - 1) * @PageSize )+ 1) AND @Index*@PageSize
sql-server performance

5 respostas

  1. Best Answer

    Uma alternativa para testar pode ser:

    ;WITH x AS (SELECT EmpID, k = ROW_NUMBER() OVER (ORDER BY EmpID) FROM dbo.Emp)
SELECT e.columns
FROM x INNER JOIN dbo.Emp AS e
ON x.EmpID = e.EmpID
WHERE x.k BETWEEN (((@Index - 1) * @PageSize) + 1) AND @Index * @PageSize
ORDER BY ...;

    Sim, você atinge a tabela duas vezes, mas no CTE onde você varre a tabela inteira, você está apenas pegando a chave, não TODOS os dados. Mas você realmente deve olhar para este artigo:

    http://www.sqlservercentral.com/articles/T-SQL/66030/

    E a continuação da discussão:

    http://www.sqlservercentral.com/Forums/Topic672980-329-1.aspx

    No SQL Server 2012, é claro, você pode usar a nova sintaxe OFFSET/ :FETCH NEXT

    ;WITH x AS 
(
  SELECT EmpID FROM dbo.Emp
    ORDER BY EmpID
    OFFSET  @PageSize * (@Index - 1) ROWS
    FETCH NEXT @PageSize ROWS ONLY
)
SELECT e.columns
FROM x INNER JOIN dbo.Emp AS e
ON x.EmpID = e.EmpID
ORDER BY ...;

    Eu também escrevi sobre isso com mais detalhes aqui:

    • 20

  2. Embora você possa não conhecer o mecanismo por trás disso, você pode testar isso comparando o desempenho de sua consulta com: select * from Employee.

    As versões mais recentes do SQL Server fazem um bom trabalho de otimização, mas isso pode depender de vários fatores.

    O desempenho da sua função ROW_NUMBER será orientado pela cláusula Order By. No seu exemplo, a maioria acharia que EmpID é a chave primária.

    Existem algumas cláusulas where que são tão complexas e/ou mal codificadas ou indexadas, você pode ser melhor apenas retornar todo o conjunto de dados (é raro e pode ser corrigido). Usar BETWEEN tem problemas.

    Antes de assumir que seria melhor retornar todas as linhas ao seu aplicativo e deixá-lo descobrir, você deve trabalhar na otimização de sua consulta. Confira as estimativas. Pergunte ao Analisador de Consultas. Teste algumas alternativas.

    • 2

  3. Eu sei que a pergunta é sobre row_number(), mas eu quero adicionar um novo recurso do sql server 2012. No sql server 2012 novo recurso OFFSET Fetch introduzido em seguida e é muito rápido do que row_number(). Eu usei e me deu um bom resultado espero que vocês também preencham a mesma experiência.

    Encontrei um exemplo em http://blogfornet.com/2013/06/sql-server-2012-offset-use/

    que é útil. Espero que ajude você também para implementar novos recursos ....

    • 2

  4. Eu não acho que avalia para retornar todas as linhas na tabela original. O SQL Server otimiza. Caso contrário, levará muito tempo para selecionar um milhão de entradas. Atualmente estou usando isso e é muito mais rápido do que selecionar todas as linhas. Então, com certeza não recebe todas as linhas. No entanto, é mais lento do que apenas buscar as primeiras cinco linhas, provavelmente devido ao tempo gasto no pedido

    • -2 
  5. DECLARE @PageIndex int;
DECLARE @PageSize int;
SET @PageIndex = 4;
SET @PageSize = 5;
;With ranked AS   --- Or you can make it a view
(
   SELECT ROW_NUMBER() OVER(ORDER BY IdentityId) AS RowNum,  *
   FROM logeventnew
)
SELECT *   --Your fields here
FROM Ranked
WHERE RowNum BETWEEN ((@PageIndex - 1) * @PageSize + 1)
    AND (@PageIndex * @PageSize)
ORDER BY IdentityId
    • -2

relate perguntas