Início / dba / Perguntas / 184086
Accepted
JohnG
Asked: 2017-08-23 06:29:55 +0800 CST

Um problema de desempenho ao consultar uma tabela grande usando um índice PK/clustered

  • 772

Para simplificar a situação, considerarei apenas uma tabela grande...
Todas as noites, uma loja enviará todos os dados novos e alterados de um dia para uma tabela grande para a matriz. (essa porção é boa) Além disso, a loja envia um resumo dessa tabela dos últimos 30 dias para a matriz dessa mesa grande.

Na sede, os dados novos e alterados são atualizados na tabela grande (sem problemas aqui). O resumo dos últimos 30 que são recebidos e carregados em uma tabela. Ele é então comparado a uma consulta que resume os dados na matriz dessa tabela muito grande (que contém todas as lojas) para essa mesma loja.<-- é aí que está o problema. Isso é feito para garantir que os dados da loja correspondam aos dados da sede dessa loja (recebemos um aviso se não corresponderem para os quais eles precisam agir)

O problema é que a consulta de resumo leva muito tempo... Estou procurando mudar a maneira como comparamos a tabela de armazenamento com a tabela de host de uma maneira mais eficiente.

Experimentei a visualização indexada e os resultados foram óptimos mas o facto de terem demasiadas limitações torna praticamente impossível implementá-la em larga escala (para todos os proprietários de software, caixas registadoras, Lojas e Sedes) devido às diferentes estruturas e diferentes versões do nosso software.

Tenho tentado pensar em diferentes maneiras de garantir que os dados de uma tabela (pelo menos nos últimos 30 dias) de uma loja correspondam à matriz, mas sinto que estou girando em círculos... estou procurando idéias para me ajudar a olhar para isso de forma diferente.

Limitações: Usamos o SQL Express nas lojas e geralmente padrão nas matrizes. Não há conexão direta entre os dois bancos de dados (os dados são transferidos por meio de arquivos).

Qualquer ajuda é apreciada. obrigada

Adicionadas mais informações: Estrutura da tabela (sei que não é a ideal, é o que herdei): Data, Loja, terminal, transNum, lineNum, Qty, Amount + 194 MAIS COLUNAS. O índice PK e clusterizado é : Date, Store, terminal, transNum, lineNum

A consulta para resumir é simples:

Select Date, Store, sum(Qty) as Qty, sum(Amount) as Amt
from MyHugeTable 
where date between '2017-07-22' and '2017-08-22'
and store = '1234'
group by Date, Store;
sql-server t-sql

2 respostas

  1. Best Answer

    Se acelerar essa consulta for extremamente importante, consideraria criar um índice de cobertura:

    CREATE INDEX IX_MyHugeTable_Date_Store_Qty_Amt
    ON MyHugeTable (Date, Store)
       INCLUDE (Qty, Amt)
;

    Adicionar um novo índice afetará o tempo para adicionar, atualizar e excluir linhas da tabela. Você deve testar para determinar o impacto de adicionar esse índice ao processo de atualização noturna. Não ajudará se isso acelerar a consulta gerando os dados de resumo para cada dia, mas a lentidão na inserção e atualização de alterações diárias é mais lenta do que a consulta de resumo é acelerada. Isso seria verdade com algumas das outras sugestões dos comentários; teste para garantir que as alterações feitas não prejudiquem as operações normais.

    FYI: a ideia por trás de um índice de cobertura é simples - se um índice tiver todas as colunas às quais uma consulta se refere, o mecanismo poderá recuperar essas informações do índice sem realmente tocar na própria tabela. Esse índice deve ocupar muito menos espaço por linha do que a tabela (com cerca de 200 colunas), portanto, a consulta deve ter um desempenho muito melhor.

    Conforme observado por David Browne, seu índice incluiria não apenas as quatro colunas listadas, mas as outras 3 chaves que compõem a chave primária. Isso ocorre porque todos os índices não clusterizados em uma tabela com um índice clusterizado usam a chave de cluster para identificar o local da linha na tabela principal. Veja este link para detalhes completos. Ainda assim, o índice será muito mais restrito do que sua tabela de ~200 colunas.

    • 4

  2. Esta é uma resposta complementar e estou fazendo algumas suposições aqui, mas parece que você está tentando reduzir o tempo necessário para concluir esse processo geral. Nesse caso, você não deve se limitar apenas a descobrir como reduzir a consulta de resumo. Não estou dizendo que você não deve priorizá-lo, mas pode haver outras etapas do seu processo em que você pode economizar tempo adicional, minimizando a quantidade de desempenho que precisa extrair da consulta de resumo.

    Sugiro que você atualize seu ambiente para o SQL 2016 SP1 ou posterior , caso ainda não tenha feito isso. Isso abre muitas funcionalidades que você pode usar (mesmo com a edição Express) que provavelmente ajudará com otimizações, como Table Partitioning , Table Compression e/ou Columnstore Indexing . Esses recursos podem ser usados ​​individualmente ou em conjunto e devem fornecer algumas melhorias de desempenho, desde que você não esteja enfrentando um gargalo de CPU em seu ambiente.

    Você também pode melhorar seus processos de importação de ETL. Este artigo, Diretrizes para otimizar a importação em massa , da Microsoft aborda alguns conceitos que podem se aplicar ao seu cenário. Há muitas informações sobre o modelo de recuperação com log em massa e, se você pensar em usá-lo, também indicarei Considerações para alternar do modelo de recuperação completo ou com log em massa, que aborda a maneira correta de alternar entre o Modelos Full e Bulk-Logged Recovery.

    Há muito aqui, então, novamente, isso não é uma resposta para o seu problema imediato, mas uma tentativa de mostrar algumas outras áreas nas quais você pode melhorar ainda mais no futuro.

    • 3

relate perguntas