Início / dba / Perguntas / 283805
Accepted
Newbie-DBA
Asked: 2021-01-23 12:51:51 +0800 CST

Ajuda com o ajuste desta consulta UPDATE ou possível reescrever

  • 772

Eu tenho a consulta SQL abaixo que é executada em avg. 40 segundos e preciso de alguma orientação onde posso melhorá-lo ou possivelmente reescrever:

   Declare @status_D INT,
      Declare @StatusIP INT,
      Declare @Status_F INT,
    
    SELECT @status_D = Id From Batchtablestatus (NOLOCK) Where desc ='Value1';
    SELECT @StatusIP = Id From Batchtablestatus (NOLOCK) Where desc ='Value2';
    SELECT @Status_F = Id From Batchtablestatus (NOLOCK) Where desc ='Value3'

BEGIN TRAN

 UPdate U
        SET 
         U.[id]= @StatusIP,
         U.[EnabledDate]= Getdate()
         OUTPUT deleted.col1,
         deleted.col2,
         deleted.col3,
         deleted.col4,
         Inserted.id

FROM (

SELECT TOP 5000 * FROM BatchChangeTable WITH (ROWLOCK,UPDLOCK)
WHERE [id] IN ( @status_D ,Status_F)
ORDER BY [ModifiedTime] )U;

  COMMIT TRAN

Estou recebendo aviso do blitzcache como serialização forçada, classificação Expensive, non-sargable e Merge join "Mant to Many" = True

O único índice que tínhamos está em id, que é um índice NC com id como coluna de chave principal.

Vou tentar atualizar minha pergunta se plano de execução, se puder. A tabela no SELECT é de cerca de 100 milhões a partir de agora. Também tem PK/CI em col1.

Perguntei aos usuários por que fazemos * nesse TOP que puxa quase 50+ colunas, mas na atualização uso apenas menos, talvez esteja pensando errado, mas só veio à minha mente.

Adicionar índice ao tempo modificado também não parece ajudar.

Por favor, sugira o que mais pode ser verificado. Obrigado

plan- A busca do índice aconteceu no id do índice NC insira a descrição da imagem aqui

sql-server query-performance

2 respostas

  1. Best Answer

    Avisos

    • A serialização forçada é por causa do OUTPUT(sem destino ou para uma @tablevariável causará isso, mas não uma #temptabela)
    • A classificação cara é porque você está ordenando ModifiedTimesem um índice de suporte e padrão de consulta
    • O aviso não-SARGable é por causa ( se eu me lembro como escrevi o cheque corretamente ), das variáveis ​​locais.

    Como corrigi-lo

    Você precisará de um bom índice de suporte:

    CREATE INDEX super_awesome
ON dbo.BatchChangeTable
    (id, ModifiedTime)
INCLUDE
    (EnabledDate);

    Mas a maneira como a consulta é escrita INserá interpretada como um predicado de intervalo e ORDER BY ModifiedDateprovavelmente ainda causará uma operação de classificação. Você precisará alterar o formulário para usar dois predicados de igualdade com a UNION ALLpara aproveitar ao máximo o índice.

    Você também precisará reescrever sua consulta para usar SQL dinâmico para não ser atingido pelos efeitos colaterais das variáveis ​​locais .

    O produto final será mais ou menos assim:

    BEGIN TRAN;

/*I'm guessing here, address local factors*/
CREATE TABLE #output(col1 int, col2 int, col3 int, col4 int, id int)

/*Use parameterized dynamic SQL*/
DECLARE @sql nvarchar(MAX) = N''

/*Hello I am your friend*/
SET @sql += N'
UPDATE u
SET
    u.id = @StatusIP,
    u.EnabledDate = GETDATE()
OUTPUT
    deleted.col1,
    deleted.col2,
    deleted.col3,
    deleted.col4,
    Inserted.id
INTO #output 
FROM
(
    SELECT TOP (5000)
        b.*
    FROM 
    (
         SELECT 
             b2.*
         FROM BatchChangeTable AS b2 WITH (ROWLOCK, UPDLOCK)
         WHERE b2.id = @status_D
         
         UNION ALL 
         
         SELECT 
             b2.*
         FROM BatchChangeTable AS b2 WITH (ROWLOCK, UPDLOCK)
         WHERE b2.id = @Status_F
         ORDER BY b2.ModifiedTime
    ) AS b
    ORDER BY b.ModifiedTime
) AS u;
';

EXEC sp_executesql @sql,
                   N'@status_D int,
                     @StatusIP int,
                     @Status_F int',
                     @status_D,
                     @StatusIP,
                     @Status_F;

SELECT 
    o.*
FROM #output AS o

COMMIT TRAN;

    Você precisará adaptar isso um pouco para se adequar à sua situação real, mas deve levá-lo na direção certa.

    • 2

  2. ideias com base nas informações disponíveis:

    1. adicione índice não clusterizado (id, ModifiedTime) ou (ModifiedTime, id), não tenho certeza com base em suas informações
    2. tente reescrever a atualização.

    código:

    update BatchChangeTable 
        SET 
         U.[id]= @StatusIP,
         U.[EnabledDate]= Getdate()
         OUTPUT deleted.col1,
         deleted.col2,
         deleted.col3,
         deleted.col4,
         Inserted.id
where col1 in (select top 5000 col1 from BatchChangeTable WITH (ROWLOCK,UPDLOCK)
WHERE [id] IN ( @status_D ,Status_F)
ORDER BY [ModifiedTime])
    • 1

relate perguntas