Background : Uma vez por dia são criadas várias tabelas temporárias que calculam uma variedade de valores agregados diferentes. Todos eles contêm o mesmo identificador exclusivo (o campo no qual eu faria uma PRIMARY KEY). Cada tabela tem cerca de 100 mil linhas com apenas 2 a 8 colunas. Vários JOINs e UNION ALLs são então feitos nas tabelas temporárias. Todos os campos em todas as tabelas temporárias são usados. Além disso, na maioria das vezes, todas as linhas também são usadas - na pior das hipóteses, metade das linhas é usada em algum lugar e a outra metade é usada em outro lugar. Quando a consulta é concluída, os resultados são salvos no disco para que as pessoas possam acessar os dados pelo resto do dia.
Pergunta : Qual das seguintes abordagens deve ser a melhor e mais rápida:
• Não possuem índices nas tabelas temporárias.
• Ter um índice clusterizado em todas as tabelas temporárias (por meio da declaração PRIMARY KEY no identificador exclusivo).
• Tenha um índice não clusterizado no identificador exclusivo e inclua as colunas restantes em todas as tabelas temporárias.
• Os dois últimos marcadores juntos.
Pensamentos : Ao executar as quatro opções simultaneamente, cada uma tinha um custo de consulta de 25% (relativo ao lote); no entanto, quando o primeiro marcador é executado, o plano de execução (na consulta SELECT) indica que devo criar um índice não clusterizado no identificador exclusivo, incluindo as colunas restantes para cada uma das tabelas temporárias.
Estou um pouco perplexo com isso. Se eu efetivamente usar todos os campos e linhas em todas as tabelas temporárias, por que isso sugeriria isso? Um heap ou um índice clusterizado não seria melhor?
Para mim, um heap minimizaria a sobrecarga na criação de um índice e na classificação dos dados; e como eu basicamente preciso de cada linha, não há mal nenhum em fazer uma varredura de tabela.
Um índice clusterizado, por outro lado, deve melhorar a tabela temporária que é criada por meio de um JOIN em duas das outras tabelas temporárias, bem como melhorar a consulta SELECT final que depende de JOINs e UNION ALLs.
Nota relacionada : Em uma tabela grande com 400 colunas e 70 milhões de linhas - não me pergunte por que temos uma quantidade absurda de colunas - uma consulta que pegou apenas a PRIMARY KEY foi ≈50 vezes mais rápida ao utilizar um índice não clusterizado não relevante sobre o índice agrupado.
Se alguém tiver alguma dica, agradeço.
Para resumir (e incluir algumas informações dos comentários), você tem um processo que é executado uma vez por dia no início da manhã que preenche várias tabelas temporárias de 100 mil linhas com 2 a 8 colunas cada. Esta é uma pergunta ampla, mas minha reação inicial seria criar chaves primárias em todas as tabelas. Se o desempenho for bom o suficiente, então eu ficaria satisfeito. Se o desempenho não for bom o suficiente, eu investigaria mais para encontrar maneiras de melhorar o código. A remoção das chaves primárias seria tecnicamente uma opção, mas em geral eu esperaria encontrar ganhos de desempenho alterando as consultas que preenchem as tabelas temporárias.
Um heap é uma tabela sem um índice clusterizado. Observe que uma chave primária não precisa ser definida da mesma forma que um índice clusterizado. Na verdade, você pode definir um heap com uma chave primária. Até onde eu sei, isso não é uma coisa muito comum de se fazer. Existem algumas vantagens em usar heaps para sua carga de trabalho:
SELECT INTOsintaxe. Observe que o otimizador de consulta pode optar por não usar inserções paralelas, dependendo de vários motivos, incluindo o tamanho estimado dos dados e asMAXDOPconfigurações.Em geral, eu não me preocuparia com uma espécie de 100 mil linhas, especialmente se esse processo for executado no início da manhã sem mais nada. É improvável que as inserções paralelas sejam uma grande ajuda também para uma quantidade tão pequena de dados.
Existem algumas desvantagens de usar heaps para essa carga de trabalho:
Difícil dizer mais sobre esses pontos porque não sei nada sobre o seu processo. É possível que suas consultas nas tabelas temporárias possam se beneficiar de um
MERGE JOIN, mas duvido que faça uma grande diferença no seu volume.Agora vamos falar sobre tabelas com um índice clusterizado. Como afirmado anteriormente, um índice clusterizado não precisa corresponder à chave primária, mas assumirei o padrão mais comum, que é ambos iguais. É com isso que você acaba quando define um
PRIMARY KEYinline como parte da definição da tabela.Existem algumas vantagens das tabelas com índices clusterizados para sua carga de trabalho:
Existem algumas desvantagens de tabelas com índices clusterizados para sua carga de trabalho:
As vantagens e desvantagens são principalmente o oposto exato de uma tabela de heap, o que não deve ser surpreendente.
A terceira e quatro opções não valem a pena considerar. Você pode criar um índice que inclua todas as colunas, mas duplicará os dados. Basta definir sua chave primária da maneira correta e você não deve usar um índice não clusterizado adicional que inclua todas as colunas. Observe que o SQL Server Management Studio não recomendará que você crie um índice clusterizado em uma tabela.
Uma diferença entre o índice clusterizado/chave primária e o índice não clusterizado é que o índice clusterizado/primário não permitirá valores NULL nas colunas de chave e impõe automaticamente uma restrição de exclusividade. Obviamente, é possível criar um índice não clusterizado com as mesmas restrições.
Em última análise, tudo o que você pode fazer é testar sua carga de trabalho com as diferentes opções. Portanto, não podemos dizer definitivamente qual abordagem será mais rápida. Com um conjunto de dados e consultas, a abordagem de heap pode ser mais rápida. Com um conjunto diferente de dados e consultas, a abordagem de tabela em cluster pode ser mais rápida. Não desconte o valor da integridade dos dados, mas você pode ter outras maneiras de impor a integridade dos dados.