Performancebremse AFTER DELETE Trigger

[Thomas Steinmaurer]

Hallo zusammen,<br><br>
sorry für das nun folgende längere Posting, aber irgendwie stehe ich am Schlauch. Ich habe eine Tabelle Accommodation mit einem Primärschlüssel auf einem Identity Feld ID. Ich möchte nun DELETE Operationen auf dieser Tabelle in zwei unterschiedliche Tabellen mitprotokollieren, wobei die erste Log-Tabelle allgemeine Informationen wie den Zeitstempel, Benutzername, Primärschlüsselwert, ... beinhaltet. Die zweite Log-Tabelle referenziert die erste und speichert konkrete protokollierte Spaltenwerte. Der AFTER DELETE Trigger für die Tabelle Accommodation sieht in etwa wie folgt aus:
<pre>
CREATE TRIGGER TRI_ACCOMMODATION_L_D ON ACCOMMODATION
AFTER DELETE NOT FOR REPLICATION AS
BEGIN
SET NOCOUNT ON

IF (EXISTS(SELECT * FROM DELETED))
BEGIN
DECLARE @VAR_ID INT
DECLARE @PKEY1_VALUE VARCHAR(20)
DECLARE @COLUMN_VALUE VARCHAR(255)
DECLARE DELETED_CURSOR CURSOR FOR SELECT ID FROM DELETED
OPEN DELETED_CURSOR
FETCH NEXT FROM DELETED_CURSOR INTO @PKEY1_VALUE
WHILE @@FETCH_STATUS = 0
BEGIN
-- An entry in the operation log table
EXEC P_INSERTINTOMSSQLLM$OPERATIONLOG 'ACCOMMODATION', 'D', 'ID', @PKEY1_VALUE, @VAR_ID = @VAR_ID OUTPUT

-- For each registered column an entry in the column log table
SELECT @COLUMN_VALUE = ID FROM DELETED WHERE ID = @PKEY1_VALUE
EXEC P_INSERTINTOMSSQLLM$COLUMNLOG @VAR_ID, 'ID', @OLD_VALUE = @COLUMN_VALUE

SELECT @COLUMN_VALUE = NAME FROM DELETED WHERE ID = @PKEY1_VALUE
EXEC P_INSERTINTOMSSQLLM$COLUMNLOG @VAR_ID, 'NAME', @OLD_VALUE =
@COLUMN_VALUE

SELECT @COLUMN_VALUE = NUMBEROFBEDS FROM DELETED WHERE ID =
@PKEY1_VALUE
EXEC P_INSERTINTOMSSQLLM$COLUMNLOG @VAR_ID, 'NUMBEROFBEDS', @OLD_VALUE
= @COLUMN_VALUE

....
FETCH NEXT FROM DELETED_CURSOR INTO @PKEY1_VALUE
END
CLOSE DELETED_CURSOR
DEALLOCATE DELETED_CURSOR

END
END
</pre>
Die Stored Procedure P_INSERTINTOMSSQLLM$OPERATIONLOG macht einfach ein INSERT INTO in die erste Log-Tabelle, und gibt die Identity des eingefügten Log-Datensatzes zurück, um danach etwas in die zweite Log-Tabelle eintragen zu können. Der Grund warum ich einen CURSOR verwende ist, dass ich einen Row-Level-Trigger simulieren möchte, d.h. wenn z.B. mit einem DELETE FROM ... 1000 Datensätze gelöscht werden, dann möchte ich auch 1000 Log-Datensätze in der ersten Log-Tabelle haben, und bei jedem Durchlauf werden auch die entsprechenden protokollierten Spaltenwerte in der zweiten Log-Tabelle eingetragen. Das Problem an der Sache ist nun, dass ein Löschen von 1000 Datensätzen in Accommodation über eine Minute dauert. Ich benutze den selben Triggeraufbau auch in einem AFTER INSERT Trigger (die temp. Tabelle DELETED ist durch INSERTED ausgetauscht), und hierbei wird ein Einfügen von 1000 Datensätzen in ca. einer Sekunde, inkl. Protokollierung durchgeführt. Das Einfügen über P_INSERTINTOMSSQLLM$COLUMNLOG ist nicht das Problem. Es sieht eher danach aus, dass SELECT @COLUMN_VALUE = ID FROM DELETED WHERE ID = @PKEY1_VALUE
die Bremse ist. Nochmals, das selbe Konstrukt, jedoch INSERTED statt DELETED ist pfeilschnell in einem AFTER INSERT Trigger.
<br><br>
Hat jemand eine Ahnung, warum der AFTER DELETE Trigger sich so bremsend auswirkt?
<br><br>
Besten Dank,<br>
Thomas

[Andreas Kosch]

Hallo,

generell warnt Microsoft vor dem Einsatz eines CURSOR, da dieser immer eine Leistungsbremse mit sich bringt. Allerdings kann man einen CURSOR beschleunigen, indem "handoptimiert" wird. Die erweiterte Syntax dazu lautet wie folgt:
<pre>
DECLARE cursor_name CURSOR
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,...n ] ] ]
</pre>
In einem Beispiel sieht das dann so aus, hier sorgt <b>FAST_FORWARD</b> dafür, das die Sache beschleunigt wird. Eine weitere Verbesserung sollte die Deklaration als <b>STATIC</b> bringen:
<pre>
DECLARE cur_KdnNr <b>CURSOR FAST_FORWARD</b> FOR SELECT CAST(KdnNr AS VARCHAR(4)) FROM konto ORDER BY KdnNr
OPEN cur_KdnNr
FETCH NEXT FROM cur_KdnNr INTO @sTmp
WHILE (@@FETCH_STATUS=0)
BEGIN
SELECT @liste=@liste + @sTmp + ','
FETCH NEXT FROM cur_KdnNr INTO @sTmp
END
CLOSE cur_KdnNr
DEALLOCATE cur_KdnNr
</pre>

Alternativ wäre auch zu überlegen, ob nicht ein <b>INSTEAD OF</b>-Trigger (dies ist eine Besonderheit des MS SQL Server) besser für diesen Job geeignet ist. Dieser Trigger "fängt" die direkte Schreibzugriffe auf die Basistabellen ab, so dass nur der Trigger arbeitet:
<pre>
CREATE TRIGGER trTLBMASTER ON TLBMASTER INSTEAD OF DELETE
AS
BEGIN
-- Schritt 1: Zuerst die Detail-Datensätze löschen
DELETE FROM TLBDETAIL
WHERE MID IN (SELECT MID FROM deleted)
-- Schritt 2: Danache den Master-Datensatz löschen
DELETE FROM TLBMASTER
WHERE MID IN (SELECT MID FROM deleted)
END
GO
</pre>
&#10

[Thomas Steinmaurer]

Hallo Andreas,<br><br>
ich sehe keine andere Möglichkeit, als dass ich einen CURSOR verwende, um einen Row-Level-Trigger zu "simulieren", den ich in meinem konkreten Fall benötige. Auf die unterschiedlichen Optionen bei der Deklaration eines CURSORs bin ich nach meinem Posting gestoßen, und verwende seitdem auch FAST_FORWARD, da der CURSOR bei mir nicht änderbar sein muss, und die Navigation unidirektional vom Anfang bis zum Ende stattfindet, d.h. FAST_FORWARD ist für mich genau richtig. Danke für den Tipp!
<br><br>
Ich weiß nicht recht, ob ein INSTEAD OF-Trigger für meinen Einsatzzweck (Logging/Auditing) die richtige Wahl ist, weil die Änderungen auf die Basistabelle sehr wohl durchgehen sollen. Ich war der Meinung, dass für einen Auditing/Logging Mechanismus ein AFTER Trigger besser geeignet sei, wobei man hier leider mit Einschränkungen bzgl. unterstützten Datentypen leben muss. LOB-Datentypen werden in einem AFTER Trigger nämlich nicht unterstützt. ;-(
<br><br>
Besten Dank und Gruß,
<br>
Thoma