Início / dba / Perguntas / 280899
Accepted
ChrisF
Asked: 2020-12-05 03:29:41 +0800 CST

Impedir que o SQL aparentemente execute uma subconsulta cara

  • 772

Eu tenho um procedimento armazenado do formulário:

CREATE PROCEDURE StoredProcedure
    @SearchString NVARCHAR(MAX),
    @ComparisonType INT
AS
BEGIN

    SELECT  A.Value1,
            B.Value1,
            C.Value1,
            ...
    FROM TableA as A
    INNER JOIN TableB as B on B.Key1 = A.Key1
    INNER JOIN TableC as C on C.Key2 = B.Key2
    WHERE A.DateValue > '2020-10-01'
    And 1 = CASE @SearchString
        WHEN N'' THEN 1
        ELSE
            CASE @ComparisonType
                WHEN 0 THEN -- potentially expensive query 1 referencing TableC
                WHEN 1 THEN -- potentially expensive query 2 referencing TableC
                WHEN 6 THEN -- potentially expensive query 3 referencing TableC
                WHEN 8 THEN -- potentially expensive query 4 referencing TableC
                WHEN 10 -- potentially expensive query 5 referencing TableC
                ELSE 0
            END
        END
END

Mesmo quando é chamado com @SearchStringdefinido para N''a consulta, leva um minuto e meio para ser executado. Se eu remover as partes da "consulta potencialmente cara", a consulta retornará os dados esperados praticamente instantaneamente.

Pelo que li, o acima não deve avaliar nada na ELSEparte da CASEdeclaração externa, @SearchStringentão N''por que ela age como se fosse?

Se isso estiver funcionando conforme o esperado, como codifico isso para forçar o SQL a não executar as partes de "consulta potencialmente cara"?

Estou tentando entender por que eles parecem afetar a consulta principal, apesar dos meus esforços para garantir que eles não sejam chamados.

sql-server query-performance

2 respostas

  1. Se você adicionar OPTION (RECOMPILE)ao final da WHEREcláusula para que fique assim, o procedimento será executado mais rápido agora?

    WHERE A.DateValue > '2020-10-01'
    And 1 = CASE @SearchString
        WHEN N'' THEN 1
        ELSE
            CASE @ComparisonType
                WHEN 0 THEN -- potentially expensive query 1 referencing TableC
                WHEN 1 THEN -- potentially expensive query 2 referencing TableC
                WHEN 6 THEN -- potentially expensive query 3 referencing TableC
                WHEN 8 THEN -- potentially expensive query 4 referencing TableC
                WHEN 10 -- potentially expensive query 5 referencing TableC
                ELSE 0
            END
        END
OPTION (RECOMPILE)

    Nesse caso, você provavelmente tem um problema de detecção de parâmetros que pode estar causando a geração de planos de execução ruins com estimativas de cardinalidade ruins. Se você atualizar sua postagem com seu plano de execução, podemos identificar exatamente como isso está acontecendo.

    Se for esse o caso, este é um bom artigo sobre Parameter Sniffing e OPTION (RECOMPILE) .

    Observe que eu geralmente uso apenas OPTION (RECOMPILE) para depuração para confirmar rapidamente um problema de detecção de parâmetro e tentar resolver o problema subjacente. Existem casos de uso para OPTION (RECOMPILE), no entanto, é uma compensação por pequenos acertos de desempenho, pois a consulta é recompilada toda vez que é executada.

    Brent Ozar tem uma série incrível de posts no blog sobre questões de Parameter Sniffing. Muitos para vincular individualmente para ser honesto, então aqui está a página raiz que vincula os principais importantes: Parameter Sniffing

    • 2
  2. Best Answer

    Inspecione o @ComparisonTypevalor primeiro para obter o valor do parâmetro desejado e, em seguida, use esse valor na consulta original.

    Isso permitirá que o SQL Server execute apenas a consulta necessária sempre que o procedimento de armazenamento for chamado.

    CREATE PROCEDURE StoredProcedure
    @SearchString NVARCHAR(MAX),
    @ComparisonType INT
AS
BEGIN
DECLARE @result SMALLINT;
IF (@SearchString = '')
BEGIN
   SET   @result = 0;
END
ELSE
    BEGIN
      IF (@ComparisonType = 0)
        BEGIN
           SELECT @result =  A   FROM TableC
        END
      IF (@ComparisonType = 1)
        BEGIN
           SELECT  @result =  B   FROM TableC
        END
      IF (@ComparisonType = 6)
        BEGIN
           SELECT  @result =  C   FROM TableC
        END
      IF (@ComparisonType = 10)
        BEGIN
           SELECT  @result =  D   FROM TableC
        END                                        
      ELSE
      BEGIN
         SET   @result = 0;
      END
    END
    SELECT  A.Value1,
            B.Value1,
            C.Value1
    FROM TableA as A
    INNER JOIN TableB as B on B.Key1 = A.Key1
    INNER JOIN TableC as C on C.Key2 = B.Key2
    WHERE A.DateValue > '2020-10-01'
    And 1 = @result
END
    • 1

relate perguntas