Início / dba / Perguntas / 343780
Accepted
coder rock
Asked: 2024-11-22 08:14:13 +0800 CST

Minha consulta está tendo problemas de desempenho de detecção de parâmetros?

  • 772

Minha consulta está tendo problema de desempenho de Parameter Sniffing? Eu mantive meu índice não clusterizado sugerido pelo plano de execução da consulta, mas ainda tenho dúvidas se isso é parameter sniffing ou outra coisa. Por favor, verifique a consulta abaixo:

declare @orgid int=22,
@salesperson int=0

select
pd.col1,dd.col1
from t1 pd
inner join (select max(doId) doid,personId from t2 where productId=99 and personId>0 
 and effectDate IS NOT NULL  group by personId) d on pd.personId=d.personId
join t2 dd on d.doid=dd.doId
join (select max(requestId) requestid,doId from  t3 group by doId ) p on dd.doId=p.doId
join t3 pp on p.requestid=pp.requestIdjoin person prn on cp.personId=prn.personId
 where pd.organizationId=@orgId
 and (@salesperson=0 or cp.personId=@salesperson)
 order by pd.patientName

Essa linha criará um problema?

(@salesperson=0 or cp.personId=@salesperson)
sql-server

1 respostas

  1. Best Answer

    não

    Você não tem um problema de sniffing de parâmetros porque não tem parâmetros. Você está usando variáveis ​​locais, o que significa que sempre obterá a mesma estimativa de vetor de densidade, não importa quais valores use.

    Talvez você queira dar uma olhada nesta sessão de perguntas e respostas:

    cheirar vs sensibilidade

    É útil entender que o sniffing de parâmetros é geralmente uma coisa boa. As pessoas costumam falar negativamente sobre isso, quando na realidade o problema que elas estão tendo é a sensibilidade dos parâmetros.

    Se os dados tocados pelos seus parâmetros forem altamente distorcidos em relação a alguns valores e menos em relação a outros, compartilhar planos para esses planos de consulta pode levar a problemas de desempenho.

    ackshualmente

    O problema real que você está tendo é que você escreveu uma consulta catch-all com a qual o SQL Server não consegue fazer nada razoável. Ele não consegue buscar em índices e não consegue estimar bem a cardinalidade, tudo porque você escreveu uma decisão de tempo de execução na sua consulta:(@salesperson=0 or cp.personId=@salesperson)

    Para mais detalhes, veja minhas postagens aqui:

    Você pode descobrir que obtém um plano melhor se adicionar OPTION(RECOMPILE);ao final da sua consulta. Isso permitirá uma otimização chamada parameter embedding . Mesmo que você esteja usando variáveis ​​locais, a mesma regra se aplica.

    Se você quiser uma solução de longo prazo que não exija recompilação em cada execução, provavelmente você vai querer usar SQL dinâmico.

    DECLARE  
    @orgId integer = 22,
    @salesperson integer = 0,
    @sql nvarchar(MAX) = N'';

SELECT
    @sql += N'
SELECT
    pd.col1,
    dd.col1
FROM t1 AS pd
JOIN
(
    SELECT
        doid = MAX(t2.doId),
        t2.personId
    FROM t2 AS t2
    WHERE t2.productId = 99
    AND   t2.personId > 0
    AND   t2.effectDate IS NOT NULL
    GROUP BY
        t2.personId
) AS d
  ON pd.personId = d.personId
JOIN t2 AS dd
  ON d.doid = dd.doid
JOIN
(
    SELECT
        requestid = MAX(t3.requestId),
        t3.doId
    FROM t3 AS t3
    GROUP BY
        t3.doId
) AS p
  ON dd.doid = p.doid
JOIN t3 AS pp
  ON p.requestid = pp.requestid
JOIN person AS prn
  ON cp.personId = prn.personId
WHERE pd.organizationId = @orgId';

IF @salesperson <> 0
BEGIN
   SET @sql += 
       N'
AND   cp.personId = @salesperson';
END;

SET @sql += N'
ORDER BY
    pd.patientName;'

PRINT @sql;

EXECUTE sys.sp_executesql
    @sql,
  N'@orgId integer,
    @salesperson integer',
    @orgId,
    @salesperson;

    uma reescrita potencial

    Se você tiver bons índices de suporte, talvez queira tentar esta versão da consulta. Você saberá bem rápido se seus índices estão quentes ou não, mas ajustá-los é uma questão separada.

    DECLARE  
    @orgId integer = 22,
    @salesperson integer = 0,
    @sql nvarchar(MAX) = N'';

SELECT
    @sql += N'
SELECT
    pd.col1,
    dd.col1
FROM t1 AS pd
CROSS APPLY
(
    SELECT
        doid = MAX(t2.doId),
        t2.personId
    FROM t2 AS t2
    WHERE t2.productId = 99
    AND   t2.personId > 0
    AND   t2.effectDate IS NOT NULL
    AND   t2.personId = pd.personId
    GROUP BY
        t2.personId
) AS d
JOIN t2 AS dd
  ON d.doid = dd.doid
CROSS APPLY
(
    SELECT
        requestid = MAX(t3.requestId),
        t3.doId
    FROM t3 AS t3
    WHERE t3.doid = p.doid
    GROUP BY
        t3.doId
) AS p
JOIN t3 AS pp
  ON p.requestid = pp.requestid
JOIN person AS prn
  ON cp.personId = prn.personId
WHERE pd.organizationId = @orgId';

IF @salesperson <> 0
BEGIN
   SET @sql += 
       N'
AND   cp.personId = @salesperson';
END;

SET @sql += N'
ORDER BY
    pd.patientName;'

PRINT @sql;

EXECUTE sys.sp_executesql
    @sql,
  N'@orgId integer,
    @salesperson integer',
    @orgId,
    @salesperson;

    importante

    Como o SQL dinâmico que usei acima usa parâmetros, é importante testar o desempenho com o máximo de uma variedade de valores @orgIde @salespersonque serão comumente usados. Você pode realmente encontrar um problema de sensibilidade de parâmetro.

    • 4

relate perguntas