Desempenho atroz juntando tabelas INSERTED e DELETED em um gatilho

Você poderia avaliar usando explícito INNER MERGE JOINou INNER HASH JOINdicas, mas dado que você provavelmente está usando essas tabelas novamente mais tarde no gatilho, provavelmente é melhor apenas inserir o conteúdo de insertede deletedtabelas em tabelas indexadas #tempe terminar com isso.

Eles não obtêm índices úteis criados para eles automaticamente.

Sei que isso foi respondido, mas acabou de aparecer como ativo recentemente e também encontrei isso em tabelas com muitos milhões de linhas. Apesar de não descartar a resposta aceita, posso pelo menos acrescentar que minha experiência mostra que um fator chave no desempenho do Trigger ao fazer testes semelhantes (ver se uma ou mais colunas realmente tiveram seus valores alterados) é se a(s) coluna(s) sendo testados eram, na verdade, parte da UPDATEdeclaração. Descobri que comparar colunas entre as tabelas insertede deletedque de fato não faziam parte da UPDATEinstrução causava um grande empecilho no desempenho que, de outra forma, não existiria se esses campos fizessem parte doUPDATEdeclaração (independentemente de seu valor realmente ser alterado). Por que todo esse trabalho (ou seja, uma consulta para comparar N campos em X linhas) para determinar se algo mudou se você pode descartar logicamente a possibilidade de qualquer uma dessas colunas ser alterada, o que obviamente não é possível se elas não estivessem presentes na SETcláusula da UPDATEdeclaração.

A solução que empreguei foi usar a função UPDATE() que só funciona dentro de Triggers. Essa função interna informa se uma coluna foi especificada na UPDATEinstrução e pode ser usada para sair do Trigger se as colunas com as quais você está preocupado não fizerem parte do arquivo UPDATE. Isso pode ser usado em conjunto com um SELECTpara determinar se essas colunas, assumindo que estão presentes no UPDATE, têm alterações reais. Eu tenho um código na parte superior de vários gatilhos de auditoria que se parece com:

-- exit on updates that do not update the only 3 columns we ETL
IF (
     EXISTS(SELECT 1 FROM DELETED) -- this is an UPDATE (Trigger is AFTER INSERT, UPDATE)
     AND (
            NOT (UPDATE(Column3) OR UPDATE(Column7)
                 OR UPDATE(Column11)) -- the columns we care about are not being updated
            OR NOT EXISTS(
                        SELECT 1
                        FROM INSERTED ins
                        INNER JOIN DELETED del
                                ON del.KeyField1 = ins.KeyField1
                                AND del.KeyField2 = ins.KeyField2
                        WHERE ins.Column3 <> del.Column3
                                 COLLATE Latin1_General_100_CS_AS -- case-sensitive compare
                        OR    ISNULL(ins.Column7, -99) <> 
                                 ISNULL(del.Column7, -99) -- NULLable INT field
                        OR    ins.[Column11] <> del.[Column11] -- NOT NULL INT field
                      )
          )
    )
BEGIN
    RETURN;
END;

Essa lógica prosseguirá para o restante do gatilho se:

1. A operação é umaINSERT
2. Pelo menos um dos campos relevantes está na SETcláusula de um UPDATE e pelo menos uma dessas colunas em uma linha foi alterada

O NOT (UPDATE...) OR NOT EXISTS()pode parecer estranho ou reverso, mas foi projetado para evitar fazer isso SELECTnas tabelas insertede deletedse nenhuma das colunas relevantes fizer parte do UPDATE.

Dependendo de suas necessidades, a função COLUMNS_UPDATED() é outra opção para determinar quais colunas fazem parte da UPDATEinstrução.

Eu posso tentar reescrever usando se existir

IF EXISTS (SELECT TOP 1 i.CUSTNMBR     
            FROM INSERTED i         
            INNER JOIN DELETED d             
            ON i.CUSTNMBR = d.CUSTNMBR and d.custclass = 'Misc'  
            WHERE d.CUSTCLAS <>i.CUSTCLAS)    
BEGIN

--do your triggerstuff here
END

http://dave.brittens.org/blog/writing-well-behaved-triggers.html

De acordo com Dave, você deve usar tabelas temporárias ou variáveis ​​de tabela com índices, porque as tabelas virtuais INSERTED/DELETED não possuem nenhum. Se você tiver a possibilidade de gatilhos recursivos, use variáveis ​​de tabela para evitar colisões de nomes.

Espero que alguém ache isso útil, pois a postagem original foi feita há algum tempo ...

O código a seguir pode aumentar o desempenho desse gatilho. Eu não sabia o tipo de dado correto da coluna [custclass] , então você precisa ajustá-lo.

DECLARE @i AS TABLE (CUSTNMBR VARCHAR(31) NOT NULL PRIMARY KEY, custclass VARCHAR(10) NOT NULL)
DECLARE @d AS TABLE (CUSTNMBR VARCHAR(31) NOT NULL PRIMARY KEY, custclass VARCHAR(10) NOT NULL)
INSERT INTO @i SELECT CUSTNMBR, custclass FROM inserted
INSERT INTO @d SELECT CUSTNMBR, custclass FROM deleted
IF NOT EXISTS
  (SELECT * FROM @i AS i INNER JOIN @d AS d ON d.CUSTNMBR = i.CUSTNMBR
   WHERE i.custclass <> d.custclass) RETURN

Observe que você pode incluir colunas adicionais nessas cópias de memória das tabelas inseridas e excluídas se precisar delas em seu código de gatilho. As chaves primárias nessas tabelas aumentarão muito o desempenho da junção ao atualizar mais do que algumas linhas de uma vez. Boa sorte!