Pergunta sobre sql, sql-server-2005, sql-server – Maneira eficiente de obter @@ rowcount de uma consulta usando row_number

30

Eu tenho uma consulta cara usando a funcionalidade row_number over () no SQL Server 2005. Eu retorno apenas uma sub-lista desses registros como a consulta é paginada. No entanto, gostaria de retornar também o número total de registros, não apenas o subconjunto paginado. Executar a consulta efetivamente duas vezes para obter a contagem está fora de questão.

A seleção de count (*) também está fora de questão, pois o desempenho é absolutamente terrível quando eu tentei isso.

O que eu realmente amo é @@ ROW_NUMBERROWCOUNT :-)

Sua resposta

4   a resposta
4

Se count (*) estiver lento, você realmente precisa resolver esse problema primeiro, examinando cuidadosamente seus índices e certificando-se de que suas estatísticas estejam atualizadas.

Na minha experiência, não há nada melhor do que fazer duas consultas separadas, uma para obter a página de dados e outra para obter a contagem total. Usar uma tabela temporária para obter contagens totais é uma estratégia perdida à medida que o número de linhas aumenta. Por exemplo, o custo de inserir 10.000.000 milhões de linhas em uma tabela temporária para contá-las obviamente será excessivo.

Embora concorde inteiramente com você, essa consulta é complexa e, nesse caso, preciso seguir o caminho da otimização. Phil Bennett
36

Ao longo dos anos, uma pilha de suor de desenvolvedor entrou em um conjunto de resultados de paginação eficiente. No entanto, não há uma resposta - depende do seu caso de uso. Parte do caso de uso está obtendo sua página com eficiência, parte é descobrir quantas linhas estão em um conjunto de resultados completo. Sinto muito se eu me atrapalho um pouco em paginação, mas os dois estão muito bem acoplados em minha mente.

Existem muitas estratégias, a maioria das quais é ruim se você tiver algum tipo de volume de dados e não se encaixar no caso de uso. Enquanto isso não é uma lista completa, a seguir estão algumas das opções .....

Executar separadoCount(*)execute uma consulta separada que faça um simples "select count (*) from MyTable"Simples e fácil para uma pequena mesabom em uma tabela grande não filtrada que é estreita ou tem um índice compacto não agrupado que você pode usarquebra quando você tem um complicadoWHERE/JOIN critérios porque executando oWHERE/JOIN duas vezes é caro.divide em um índice amplo porque o número de leituras aumenta.CombinarROW_Number() OVER() eCOUNT(1) OVER(PARTITION By 1)Isto foi sugerido por @RBarryYoung. Tem a vantagem de ser simples de implementar e muito flexível.O lado negativo é que há muitas razões para isso se tornar extremamente caro rapidamente.Por exemplo, em um DB que estou trabalhando atualmente, há uma tabela de Mídia com cerca de 6.000 linhas. Não é particularmente amplo, tem um PK inteiro em cluster e, além de um índice exclusivo compacto. No entanto, um simplesCOUNT(*) OVER(PARTITION BY 1) as TotalRows resulta em ~ 12.000 leituras. Compare isso com um simplesSELECT COUNT(*) FROM Media - 12 leituras. Wowzers

ATUALIZAÇÃO - a questão das leituras que mencionei é um pouco de falsidade. Acontece que, com as funções de janela, a unidade usada para medir as leituras é um tanto quanto mista. O resultado final é o que parece ser um grande número de leituras. Você pode ver mais sobre o assunto aqui:Por que as leituras lógicas para funções agregadas em janelas são tão altas?

Tabelas temporárias / variáveis ​​de tabelaHá muitas estratégias que levam um conjunto de resultados e inserem chaves ou segmentos relevantes de resultados em variáveis ​​de tabelas / tabela temporárias.Para conjuntos de resultados pequenos / médios, isso pode fornecer ótimos resultados.Esse tipo de estratégia funciona em praticamente qualquer plataforma / versão do SQL.Operar em um conjunto de resultados várias vezes (muitas vezes um requisito) também é fácil.O lado negativo é quando se trabalha com grandes conjuntos de resultados ... inserir alguns milhões de linhas em uma tabela temporária tem um custo.Compondo o problema, em uma pressão de sistema de alto volume no TempDB pode ser um fator bastante importante, e as tabelas temporárias estão efetivamente trabalhando no TempDB.Soma gaussiana / número de linha duplaEsta ideia dependesubconjunto de algo que o matemático Gauss descobriu (como somar uma série de números). O subconjunto é como obter contagem de linhas de qualquer ponto da tabela.De uma série de números (Row_Number()) a contagem de linhas de 1 a N é(N + 1) - 1. Mais explicações nos links.A fórmula parece que seria líquida para apenas N, mas se você mantiver a fórmula, coisas interessantes acontecem, você pode descobrir a contagem de linhas a partir de uma página no meio da tabela.O resultado líquido é que você fazROW_Number() OVER(Order by ID) eROW_Number() OVER(Order by ID DESC) em seguida, some os dois números e subtraia 1.Usando minha tabela de Mídia como exemplo, minhas leituras caíram de 12.000 para cerca de 75.Em uma página maior, você acabou repetindo os dados muitas vezes, mas o deslocamento nas leituras pode valer a pena.Eu não testei isso em muitos cenários, por isso pode desmoronar em outros cenários.Superior (@n) / SET ROWCOUNTEssas não são estratégias específicas, mas são otimizações baseadas no que sabemos sobre o otimizador de consultas.Criativamente usando Top (@n) [top pode ser uma variável no SQL 2008] ou SET ROWCOUNT pode reduzir seu conjunto de trabalho ... mesmo se você estiver puxando uma página do meio de um conjunto de resultados, você ainda pode restringir o resultadoEssas idéias funcionam devido ao comportamento do otimizador de consulta ... um service pack / hotfix pode alterar o comportamento (embora provavelmente não).Em instâncias certianas SET ROWCOUNT pode ser um pouco precisoEssa estratégia não leva em conta a contagem total de linhas, apenas torna a paginação mais eficienteEntão, o que é um desenvolvedor para fazer?

Leia meu bom homem, leia. Aqui estão alguns artigos em que eu me apoiei ...

Um método mais eficiente para paginação através de grandes conjuntos de resultadosOtimizando Paging no Servidor - Parte IOtimizando Paging no Servidor - Parte IIExplicação da soma gaussianaRetornando os resultados classificados com o Microsoft SQL Server 2005ROW_NUMBER () OVER não é rápido o suficiente com um grande conjunto de resultadosRecuperando os primeiros N registros de uma consulta SQLPaginação no servidor usando o SQL Server 2005Por que as leituras lógicas para funções agregadas em janelas são tão altas?

Espero que ajude.

uau, ótimo detalhe! esta deve ser a resposta (embora não lhe dê o um :)) Kat Lim Ruiz
@Martin - Eu não classifiquei tudo isso, mas notei algumas coisas. Primeiro, eu suspeito que você está vendo em leituras de memória (ou seja, sua tabela é armazenada em cache). Em um desempenho maior, o conjunto de dados iria implodir. Em segundo lugar, parece ramificar o plano de execução para cada função em janelas (como se você tivesse feito uma junção). Terceiro, muitas vezes você acaba com muitas pesquisas importantes - veja "Solução inicial" e "métodos alternativos" no artigo "Otimizando a paginação do servidor - Parte I". EBarr
Sim, eles estão definitivamente em leituras de memória. Ele preenche o spool e reutiliza o mesmo spool com rebobinamentos e religações nas outras áreas do plano (spool de sub-expressão comum). Eu não conseguia contabilizar um número tão alto de leituras dado o plano e os dados. Martin Smith
Eu notei que as leituras lógicas para funções agregadas em janelas podem parecer muito altas também. Eles não parecem prejudiciais ao desempenho, masEu ainda gostaria de saber exatamente o porquê! para avaliar isso um pouco mais. Martin Smith
0

Eu faço isso colocando todo o conjunto de resultados com o row_number em uma tabela temporária, em seguida, use o @@ rowcount e use a consulta para retornar a página de dados de que preciso.

36

Confira o agregado COUNT (*) quando usado com OVER (PARTITON BY ..), da seguinte forma:

<code>    SELECT
     ROW_NUMBER() OVER(ORDER BY object_id, column_id) as RowNum
    , COUNT(*) OVER(PARTITION BY 1) as TotalRows
    , * 
    FROM master.sys.columns
</code>

Esta é IMHO a melhor maneira de fazer isso sem ter que fazer duas consultas.

Na verdade, muito boa solução !, embora você adicione uma coluna para a consulta que você só precisa para a primeira linha. Mas eu acho que é mais rápido e usando menos recursos do que fazer as duas consultas separadas ... Kat Lim Ruiz
Obrigado, apenas o que eu estava procurando !!! Phil Bennett

Perguntas relacionadas