Início / dba / Perguntas / 157353
Accepted
TheGameiswar
Asked: 2016-12-07 06:58:23 +0800 CST

Quebra de consulta em IF EXISTS torna muito lento

  • 772

Eu tenho a consulta abaixo:

select databasename 
from somedb.dbo.bigtable l where databasename ='someval' and source  <>'kt'
and not exists(select 1 from dbo.smalltable c where c.source=l.source)

A consulta acima é concluída em três segundos.

Se a consulta acima retornar algum valor, queremos que o procedimento armazenado EXIT, então eu o reescrevo como abaixo:

If Exists(
select databasename 
from somedb.dbo.bigtable l where databasename ='someval' and source  <>'kt'
and not exists(select 1 from dbo.smalltable c where c.source=l.source)
)
Begin
Raiserror('Source missing',16,1)
Return
End

No entanto, isso está demorando 10 minutos.

Posso reescrever a consulta acima como abaixo, que também é concluída em menos de 3 segundos:

  select databasename 
from somedb.dbo.bigtable l where databasename ='someval' and source  <>'kt'
and not exists(select 1 from dbo.smalltable c where c.source=l.source
if @@rowcount >0
Begin
Raiserror('Source missing',16,1)
Return
End

O problema com a reescrita acima é que a consulta acima faz parte de um procedimento armazenado maior e retorna vários conjuntos de resultados. Em C#, iteramos cada conjunto de resultados e fazemos algum processamento.

O acima retorna um conjunto de resultados vazio, portanto, se eu seguir essa abordagem, terei que alterar meu C# e fazer a implantação novamente.

Então minha pergunta é,

por que usar apenas IF EXISTSmuda o plano para levar tanto tempo?

Abaixo estão os detalhes que podem ajudá-lo e deixe-me saber se você precisar de algum detalhe:

  1. Criar script de tabela e estatísticas para obter o mesmo plano que o meu
  2. Plano de execução lenta

  3. Plano de execução rápida

    Plano lento usando Brentozar Cole o plano
    Plano rápido usando Brentozar Cole o plano

Nota: Ambas as consultas são iguais (usando parâmetros), a única diferença é EXISTS(posso ter cometido alguns erros ao anonimizar).

Os scripts de criação da tabela estão abaixo:

http://pastebin.com/CgSHeqXc -- estatísticas de mesa pequena
http://pastebin.com/GUu9KfpS -- estatísticas de mesa grande

sql-server

3 respostas

  1. Best Answer

    Conforme explicado por Paul White em sua postagem no blog: Inside the Optimizer: Row Goals In Depth , o EXISTSintroduz uma meta de linha, que prefere NESTED LOOPSou MERGE JOINsobreHASH MATCH

    Como exemplo final, considere que uma semijunção lógica (como uma subconsulta introduzida com EXISTS) compartilha o tema geral: ela deve ser otimizada para localizar rapidamente a primeira linha correspondente.

    Em sua consulta, isso aparentemente introduz loops aninhados e remove o paralelismo, resultando em um plano mais lento.

    Portanto, você provavelmente precisaria encontrar uma maneira de reescrever sua consulta sem usar o NOT EXISTSda sua consulta.

    Você pode reescrever sua consulta usando um LEFT OUTER JOINe verificar se não há uma linha na smalltable testando paraNULL

    If EXISTS(
    SELECT databasename
    FROM somedb.dbo.bigtable l
    LEFT JOIN dbo.smalltable c ON c.source = l.source
    WHERE databasename = 'someval'
    AND source <> 'kt'
    AND c.source IS NULL
)

    Você provavelmente EXCEPTtambém poderia usar uma consulta, dependendo de quantos campos você precisa comparar assim:

    If EXISTS(
   SELECT source
   FROM somedb.dbo.bigtable l
   WHERE databasename = 'someval'
   AND source <> 'kt'

   EXCEPT

   SELECT source
   FROM dbo.smalltable
)

    Lembre-se, Aaron Bertrand tem uma postagem no blog fornecendo motivos pelos quais ele prefere NOT EXISTS , que você deve ler para ver se outras abordagens funcionam melhor e para estar ciente dos possíveis problemas de correção no caso de valores NULL.

    Perguntas e respostas relacionadas: IF EXISTS demorando mais do que a instrução select incorporada

    • 20

  2. Eu me deparei com o mesmo problema, consegui me contornar evitando usar "EXISTS" e usando a função "COUNT ()" e a instrução "IF ... ELSE".

    Para o seu exemplo tente o seguinte:

    IF
(
    SELECT
        COUNT(l.databasename) + 1 AS databasename
    FROM somedb.dbo.bigtable AS l

    WHERE   l.databasename ='someval'
        AND l.[source]  <> 'kt'
        AND NOT EXISTS(SELECT 1 FROM dbo.smalltable AS c WHERE c.[source]=l.[source])
) > 1 --Acts like EXISTS
BEGIN
    RAISERROR('Source missing', 16, 1)
RETURN
END

    A razão pela qual estou adicionando "+ 1" à contagem é para que eu possa usar "> 1" na condição IF, usar "> 0" ou "<> 0" acionará a consulta para usar loops aninhados em vez de HASH Combine. Ainda não investiguei por que exatamente isso está acontecendo, seria interessante descobrir o porquê.

    Espero que ajude!

    • 1

  3. Você precisa reescrever sua consulta usando junções explícitas e especificar qual operação de junção deseja usar (loop, hash ou mesclagem) como esta.

    If not exists(
    select databasename 
    from somedb.dbo.bigtable l
    inner hash join dbo.smalltable c 
        on c.source = l.source
where databasename ='someval' and source  <>'kt')
begin
    Raiserror('Source missing',16,1)
    Return
end

    Ao usar EXISTS ou NOT EXISTS, o plano de consulta gerado pelo SQL Server com a operação NESTED LOOP, assumindo que ele deve percorrer todas as linhas do conjunto, uma a uma, procurando a primeira linha para satisfazer a condição. O uso de HASH JOIN irá acelerá-lo.

    • 0

relate perguntas