Início / dba / Perguntas / 131022
Accepted
MrVimes
Asked: 2016-03-03 05:22:27 +0800 CST

Execute a subconsulta uma vez / otimize a junção com a subconsulta sem precisar usar a tabela temporária

  • 772

Até hoje eu achava que tinha o jeito de otimizar consultas com junções em subconsultas. Mas venho tentando otimizar uma consulta e por capricho tentei um método antigo que achava que não precisava mais usar - usei tabelas temporárias.

Consulta lenta (leva 5 minutos)...

select (columns) from big_transactions_table t inner join 

  (select (columns) from small_info_table where (conditions)) q1

on q1.key = t.key
group by (columns)

Consulta rápida usando uma tabela temporária...

select (columns) from small_info_table into #q1 where (conditions)

go

select (columns) from big_transactions_table t inner join #q1
on #q1.key = t.key
group by (columns)

Demorou 14 segundos!

Fiquei com a impressão de que, quando você tem uma subconsulta em uma junção como essa, o mecanismo SQL obtém os dados primeiro antes de uni-los à consulta externa. Agora não tenho tanta certeza. Alguém pode me dizer porque há uma grande diferença no tempo de execução e se existe uma maneira simples de acelerar a consulta sem usar uma tabela temporária?

sql-server-2005

1 respostas

  1. Best Answer

    Fiquei com a impressão de que, quando você tem uma subconsulta em uma junção como essa, o mecanismo SQL obtém os dados primeiro antes de uni-los à consulta externa.

    O otimizador de consulta considera várias estratégias de execução física que garantem a produção dos mesmos resultados que a especificação de consulta lógica original. Portanto, não, não há inferência geral que você possa fazer a partir da forma escrita da consulta sobre como ela será realmente executada.

    A decisão do otimizador é baseada nos custos estimados, que são determinados em grande parte pelo tamanho (cardinalidade e largura) dos dados esperados em cada etapa do pipeline e pelo tipo de operação física realizada.

    Se sua consulta resultar em estimativas de custo imprecisas , a estratégia escolhida pelo otimizador provavelmente não será a ideal, talvez nem seja muito boa.

    Custos imprecisos têm muitas causas, incluindo estatísticas não representativas nos objetos base e o uso de predicados opacos ou difíceis de estimar na consulta. Com boas estatísticas, uma consulta de tamanho razoável, operações de comparação simples e um esquema relacional, as chances são muito boas de que as estimativas (e, portanto, a seleção do plano físico do otimizador) sejam boas o suficiente.

    Uma forma de simplificar o trabalho do otimizador é materializar partes de uma consulta complexa (ou que simplesmente não obtém boas estimativas) em tabelas temporárias. Eles fornecem ao otimizador informações de cardinalidade direta e o SQL Server geralmente pode criar estatísticas automaticamente na tabela temporária para fornecer mais informações válidas. Por último, mas não menos importante, a tabela temporária permite que você adicione índices específicos a ela, onde isso valer a pena.

    Quando usadas sensatamente *, as tabelas temporárias são uma ótima maneira de fornecer informações ao otimizador, produzindo planos de execução confiáveis, mesmo quando a distribuição e o tamanho dos dados subjacentes mudam.

    Se você fez tudo o que pode com estatísticas, indexação e a forma escrita da consulta, usar uma tabela temporária pode ser ideal aqui. É impossível dizer com certeza, com base nas informações fornecidas na pergunta, e sem saber o nível de especialização do autor.

    * Uso sensato significa, por exemplo, limitar o número, tamanho e largura das tabelas temporárias necessárias, bem como a frequência de uso. SELECT * INTO #temp FROM dbo.MyHugeTable;não é sensato, por exemplo.

    • 3

relate perguntas