Início / dba / Perguntas / 221041
Accepted
LCJ
Asked: 2018-10-26 13:24:00 +0800 CST

Forçar o SQL Engine a preparar o plano não nulo apenas para uma consulta que usa variável

  • 772

Eu tenho a seguir duas abordagens de consulta. A Abordagem 1 funciona em 20 segundos. A Abordagem 2 não foi concluída mesmo após o expediente.

Abordagem 1

SELECT *,
        SUBSTRING(IDAndCode,CHARINDEX ('$', IDAndCode)+1, LEN(IDAndCode) - 2) AS ICDCode
From dbo.MyTable
WHERE F.Fact_Diagnosis_BK LIKE 'SKI-CE:'+'%' 
GO

Abordagem 2

DECLARE @DelimitingCharacter CHAR(1)
SET @DelimitingCharacter = '$'; 

SELECT *,
        SUBSTRING(IDAndCode,CHARINDEX (@DelimitingCharacter, IDAndCode)+1, LEN(IDAndCode) - 2) AS ICDCode
From dbo.MyTable
WHERE F.Fact_Diagnosis_BK LIKE 'SKI-CE:'+'%' 
GO

Observação: as duas acima são consultas simplificadas. As consultas reais podem ser vistas no plano de execução abaixo.

Planos de Execução

Plano de Execução Real – Abordagem 1 https://www.brentozar.com/pastetheplan/?id=rJlcWTk3m

Plano Estimado - Abordagem 2: https://www.brentozar.com/pastetheplan/?id=SJddO3en7

Nota: O plano do Approach 2 usa Nested Loopjoin em vez de Hash Matchjoin

Pergunta

Eu sei que isso pode ser corrigido adicionando OPTION (RECOMPILE). Se bem entendi, a lentidão está acontecendo, pois o SQL Server está criando um plano para acomodar a NULLpossibilidade da variável.

No meu caso, a variável será Non-Nullantes da execução da consulta (garantida)

  1. No SQL Server 2016, quais são as possibilidades de informar ao mecanismo SQL que a variável será não nula e preparar um plano apenas para tal condição, sem usar OPTION (RECOMPILE)?

  2. A lentidão é chamada de sniffing de parâmetros? (Não há parâmetro aqui. É uma variável local em uma consulta autônoma)

Nota: estou tentando ver opções para fazer a consulta rodar mais rápido sem usar RECOMPILE.

Referências :

  1. Consulta do SQL Server: Rápida com Literal, mas Lenta com Variável
  2. Melhorando o desempenho da consulta com OPTION (RECOMPILE), Constant Folding e evitando problemas de Parameter Sniffing
  3. Sniffing de Parâmetros vs VARIABLES vs Recompile vs OPTIMIZE FOR UNKNOWN
  4. Variáveis ​​locais versus procedimentos armazenados parametrizados no SQL Server
sql-server performance

2 respostas

  1. Best Answer

    A seguir estão as três maneiras que podem corrigir o problema. Mais abordagens são bem-vindas.

    Solução 1

    Em vez de uma variável (@DelimitingCharacter), use uma tabela temporária.

    CREATE TABLE #VariableHoldingTable (DelimitingCharacter CHAR(1) NOT NULL)
INSERT INTO #VariableHoldingTable
SELECT '<'

    --E faça uma junção

      INNER JOIN #VariableHoldingTable V
    ON 1 = 1

    Plano de Execução: https://www.brentozar.com/pastetheplan/?id=rJpehe5hQ

    Solução 2

    CREATE CLUSTERED INDEX CX_DXID on #MultipleDiagnosisDXs(DXID)

    Plano de Execução: https://www.brentozar.com/pastetheplan/?id=B1nKne9nX

    Solução 3

    OPTION (RECOMPILE)

    Plano de Execução: https://www.brentozar.com/pastetheplan/?id=BkRgalqnX

    • 1 
  2. CREATE TABLE #MultipleDiagnosisDXs (DXID INT Not null)

    Observe not nullaqui.Depois de preencher, crie o índice.

    Create Clustered index IX_DXID on #MultipleDiagnosisDXs(DXID)

    Na consulta correta,

    WHERE EXISTS (SELECT DXID FROM #MultipleDiagnosisDXs A where A.DXID=B.DXID)

    Não há necessidade de order bycláusula

    Acima da mudança é preciso.

    Você deve tentar uma vez EXEC sys.sp_executesql.

    Você também deve tentar Row_Numberuma vez, com isso você não precisa da segunda instrução Select e não precisa da tabela temporária.

    Não use "*".

    Apenas tente With (Nolock)a dica: Se ajudar, discutiremos mais sobre isso. Nem sempre é considerado a melhor prática, mas tente uma vez.

    • 0

relate perguntas