Okrutna wydajność łączenia tabel INSERTED i DELETED w wyzwalaczu

Mam wyzwalacz UPDATE w tabeli, która szuka konkretnej kolumny zmieniającej się z jednej określonej wartości na dowolną inną. Gdy tak się dzieje, aktualizuje niektóre powiązane dane w innej tabeli za pomocą pojedynczej instrukcji UPDATE.

Pierwszą rzeczą, którą robi wyzwalacz, jest sprawdzenie, czy którykolwiek z zaktualizowanych wierszy zmienił wartość tej kolumny w stosunku do wartości, o której mowa. Po prostu łączy INSERTED z DELETED i porównuje wartość w tej kolumnie. Jeśli nic się nie kwalifikuje, następuje wczesne ratowanie, więc instrukcja UPDATE nie działa.

IF NOT EXISTS (
    SELECT TOP 1 i.CUSTNMBR
    FROM INSERTED i
        INNER JOIN DELETED d
            ON i.CUSTNMBR = d.CUSTNMBR
    WHERE d.CUSTCLAS = 'Misc'
        AND i.CUSTCLAS != 'Misc'
)
    RETURN

W takim przypadku CUSTNMBR jest kluczem podstawowym tabeli bazowej. Jeśli wykonam dużą aktualizację w tej tabeli (powiedzmy, ponad 5000 wierszy), to oświadczenie zajmie WIEK, nawet jeśli nie dotknąłem kolumny CUSTCLAS. Mogę obserwować, jak utknął na tym oświadczeniu przez kilka minut w Profiler.

Plan wykonania jest dziwaczny. Pokazuje Wstawione skanowanie z 3714 wykonaniami i ~ 18,5 milionami wierszy wyjściowych. To działa przez filtr w kolumnie CUSTCLAS. Łączy to (poprzez zagnieżdżoną pętlę) z usuniętym skanem (również filtrowanym w CUSTCLAS), który wykonuje się tylko raz i ma 5000 wierszy wyjściowych.

Jaką idiotyczną rzecz tutaj robię, żeby to spowodować? Pamiętaj, że wyzwalacz absolutnie musi poprawnie obsługiwać aktualizacje w wielu wierszach.

EDYCJA :

Próbowałem też napisać to w ten sposób (na wypadek, gdyby EXISTS robił coś nieprzyjemnego), ale wciąż jest tak samo okropne.

DECLARE @CUSTNMBR varchar(31)
SELECT TOP 1 @CUSTNMBR = i.CUSTNMBR
FROM INSERTED i
    INNER JOIN DELETED d
        ON i.CUSTNMBR = d.CUSTNMBR
WHERE d.CUSTCLAS = 'Misc'
    AND i.CUSTCLAS != 'Misc'

IF @CUSTNMBR IS NULL
    RETURN

Możesz ocenić za pomocą jawnych INNER MERGE JOINlub INNER HASH JOINpodpowiedzi, ale biorąc pod uwagę, że prawdopodobnie używasz tych tabel ponownie później w wyzwalaczu, prawdopodobnie lepiej jest po prostu wstawić zawartość tabel insertedi deleteddo #temptabel indeksowanych i wykonać to.

Nie otrzymują automatycznie utworzonych dla nich przydatnych indeksów.

Wiem, że odpowiedź została udzielona, ​​ale pojawiła się jako ostatnio aktywna i natknąłem się na to również w przypadku tabel z wieloma milionami wierszy. Nie pomijając zaakceptowanej odpowiedzi, mogę przynajmniej dodać, że moje doświadczenie pokazuje, że kluczowym czynnikiem w wydajności wyzwalacza podczas wykonywania podobnych testów (sprawdzanie, czy rzeczywiście zmieniono ich wartości w jednej lub więcej kolumn), jest to, czy kolumny testowane były w rzeczywistości częścią UPDATEoświadczenia. Zauważyłem, że porównywanie kolumn między tabelami insertedi deletedtabelami, które w rzeczywistości nie były częścią UPDATEinstrukcji, znacznie obniżyło wydajność, której inaczej nie byłoby, gdyby te pola były częściąUPDATEinstrukcja (niezależnie od faktycznej zmiany ich wartości). Dlaczego to wszystko działa (tj. Zapytanie o porównanie N pól w X wierszach) w celu ustalenia, czy coś się zmieniło, jeśli można logicznie wykluczyć możliwość zmiany którejkolwiek z tych kolumn, co oczywiście nie jest możliwe, gdyby nie były obecne w SETklauzuli UPDATEoświadczenia.

Rozwiązaniem, które zastosowałem, było użycie funkcji UPDATE (), która działa tylko wewnątrz Triggerów. Ta wbudowana funkcja informuje, czy kolumna została podana w UPDATEinstrukcji i może być użyta do wyjścia z wyzwalacza, jeśli kolumny, o które się martwisz, nie są częścią UPDATE. Można tego użyć w połączeniu z a, SELECTaby ustalić, czy te kolumny, zakładając, że są obecne w UPDATE, mają rzeczywiste zmiany. Mam kod na górze kilku wyzwalaczy audytu, który wygląda następująco:

-- 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;

Ta logika przejdzie do reszty wyzwalacza, jeśli:

1. Operacja jest INSERT
2. Co najmniej jedno z odpowiednich pól znajduje się w SETklauzuli UPDATE i co najmniej jedna z tych kolumn w jednym wierszu uległa zmianie

NOT (UPDATE...) OR NOT EXISTS()Może wyglądać dziwnie lub do tyłu, ale jest zaprojektowany, aby uniknąć robi to SELECTna insertedi deletedstoły, jeżeli żaden z odpowiednich kolumn są częścią UPDATE.

W zależności od potrzeb funkcja COLUMNS_UPDATED () jest kolejną opcją określającą, które kolumny są częścią UPDATEinstrukcji.

Mógłbym spróbować przepisać używając, jeśli istnieje

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

Według Dave'a powinieneś używać tabel temp lub zmiennych tabel z indeksami, ponieważ wirtualne tabele WSTAWIONO / USUWANE nie mają żadnych. Jeśli masz możliwość wyzwalania rekurencyjnego, powinieneś użyć zmiennych tabeli, aby uniknąć kolizji nazw.

Mam nadzieję, że ktoś uzna to za pomocne, ponieważ oryginalny post był jakiś czas temu ...

Poniższy kod może zwiększyć wydajność tego wyzwalacza. Nie znałem poprawnego typu danych w kolumnie [custclass], więc musisz go dostosować.

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

Zauważ, że możesz dołączyć do nich dodatkowe kolumny w kopiach pamięci wstawionych i usuniętych tabel, jeśli potrzebujesz ich w kodzie wyzwalacza. Klucze podstawowe w tych tabelach znacznie zwiększą wydajność łączenia podczas aktualizacji więcej niż kilku wierszy jednocześnie. Powodzenia!