Trigger "FOR INSERT, UPDATE" und die Performance ist im Keller

Question

Hallo *.*,

ich möchte in meiner DB einen Trigger setzen, der abhängig vom Inhalt den Wert von einer Spalte in eine von 3 anderen schreibt. Es handelt sich hierbei um Referenz-Schlüssel aus einer objektorientierten Datenbank. Da ich mit Fremdschlüsseln arbeiten möchte, muss ich an der Stelle erst mal die DB-Struktur von OO auf relational anpassen. Genau das soll der Trigger machen.

Definiert habe ich ihn mal so:

ALTER TRIGGER [dbo].[Journal] ON [dbo].[Journal]
	FOR INSERT, UPDATE 
	AS  
	BEGIN 
	SET NOCOUNT ON
	UPDATE [dbo].[Journal] SET Vorgang3 = VORGANG WHERE VORGANG LIKE '%,3,0)'
	UPDATE [dbo].[Journal] SET Vorgang4 = VORGANG WHERE VORGANG LIKE '%,4,0)'
	UPDATE [dbo].[Journal] SET Vorgang9 = VORGANG WHERE VORGANG LIKE '%,9,0)'
END;

Und damit kracht meine Performance um 90-95% ein. Da es sich um eine Tabelle mit mehreren 100K Datensätzen handelt, vermute ich, dass der SQL Server das Update nicht auf die aktuelle Zeile, sondern die ganze Tabelle macht. Liege ich da richtig? Und wie kann ich das performant an der Stelle machen?

Gruß, Markus

---

MCTS (70-642), MCP Please click the "Mark as Answer" or "Vote As Helpful button" if a post solves your problem or is helpful! Bitte klicke auf "Als Antwort vorschlagen" oder "Als hilfreich bewerten", wenn mein Beitrag Dein Problem löst oder hilfreich ist.

Answer 1

Hallo Markus,

eine Suffixsuche mit "%Irgendwas" löst immer einen Full Table/Index Scan aus und das ist natürlich inperformant, und davon hast Du gleich 3, wo ein UPDATE Statement ausreichen würde.

Zudem sind die Updates völlig unabhängig davon, welche Daten eigentlich geändert wurden, egal ob 1 Datensatz oder alle. Die eingefügten Datensätze stehen im Trigger in der virtuellen Tabelle "inserted" und die alten in "deleted", die solltest Du benutzen, um nur die betroffenen Datensätze zu erhalten; siehe Verwenden der Tabellen inserted und deleted

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

Hallo Markus,

zu Olafs Antwort den Vorschlag den Trigger so zu ändern:

ALTER TRIGGER [dbo].[Journal] 
	ON [dbo].[Journal]
	FOR INSERT, UPDATE 
AS  
BEGIN 
	SET XACT_ABORT, NOCOUNT ON;
	
	UPDATE dbo.Journal
	SET Vorgang3 = CASE WHEN RIGHT(VORGANG, 5) = ',3,0)' THEN VORGANG ELSE Vorgang3 END,
		Vorgang4 = CASE WHEN RIGHT(VORGANG, 5) = ',4,0)' THEN VORGANG ELSE Vorgang4 END,
		Vorgang9 = CASE WHEN RIGHT(VORGANG, 5) = ',9,0)' THEN VORGANG ELSE Vorgang9 END
	FROM dbo.Journal 
        -- Verknüpfung über den Primärschlüssel anpassen
	INNER JOIN inserted ON Journal.PrimaryKey = inserted.PrimaryKey
	WHERE RIGHT(inserted.VORGANG, 5) IN (',3,0)', ',4,0)', ',9,0)');
END;

Wobei eine "bessere" Verschlüsselung, die die Zeichenoperationen vermeidet, es verbessern könnte, sofern dass möglich ist.

Gruß Elmar

Answer 3

Hallo Olaf, Hallo Elmar,

danke für Eure Tips - es funktioniert jetzt wunderbar. Und noch was dazu gelernt, was mich weiter bringt :) Dankeschön!

Gruß, Markus

---

MCTS (70-642), MCP Please click the "Mark as Answer" or "Vote As Helpful button" if a post solves your problem or is helpful! Bitte klicke auf "Als Antwort vorschlagen" oder "Als hilfreich bewerten", wenn mein Beitrag Dein Problem löst oder hilfreich ist.