none
trigger ersetzen? RRS feed

  • Frage

  • Hallo Experts,

    um die Anzeige veränderter und neu eingefügter Datensätze (Veränderung von Warenbeständen, Versandinformationen) in der DB (SQL Server 2005) Ereignisgesteuert zu betreiben haben wir einige Trigger implementiert. Seit dem tauchen vermehrt Sperren und Time Outs auf. So dass der Trigger von hochfrequentierten Tabellen sein Zweckfast schon nicht erfüllen, denn ca. 10% aller betroffene Datensätze werden nicht commited. 

    Die Frage ist ob grundsätzlich die Trigger lieber nicht benutzt werden sollen, oder gibt es einen effizienteren weg einen Trigger zu implementieren? In meinem Fall (s. unten) enthellt die Prozedur einen delete und ein insert befehl aufgrund der Daten ´, die durch eine UDF zurückgegeben werden. Immer nur einzelne Datensetze

    So z. B ein Trigger der beim Vorhandensein eines Satzes in DELETED oder INSERTED Tabellen eine Prozedur ausführt:


    CREATE TRIGGER [dbo].[update_XXX]
    ON [dbo].[XXX_DATA]
    AFTER UPDATE, INSERT
    as

    declare @XXX_typ int;
    declare @XXX_nummer int;


    IF EXISTS(SELECT 1 FROM deleted where XXX_TYP in (2, 3, 4))

    BEGIN
    select @XXX_typ = d.XXX_TYP  from deleted d;
    select @XXX_nummer =d.XXX_NUMMER  from deleted d;
    END

    else

    IF EXISTS(SELECT 1 FROM INSERTED where XXX_TYP in (2, 3, 4))

    BEGIN
    select @XXX_typ = i.XXX_TYP  from INSERTED i;
    select @XXX_nummer = i.XXX_NUMMER  from INSERTED i;
    END
    if @XXX_typ not in (1,5,6)
    BEGIN
    exec proc_update_XXX_DATA_to_yyy @XXX_typ = @XXX_typ, @XXX_nummer=@XXX_nummer;
    END
    --second part: "Update Webshop"
    BEGIN
        if @XXX_typ = 3
        BEGIN
            IF EXISTS(SELECT 1 FROM deleted d inner join VERAENDERUNGEN_LOG gwa on gwa.NUMMER = d.XXX_NUMMER and d.XXX_TYP=3)
        BEGIN
        update gwa
        set gwa.INSERT_TIMSTP = getdate()
        from VERAENDERUNGEN_LOG gwa
        inner join deleted d on d.XXX_NUMMER = gwa.NUMMER
        and gwa.STATUS = 0
        END
    ELSE
        BEGIN
        INSERT INTO VERAENDERUNGEN_LOG
        SELECT XXX_NUMMER, 0, getdate()
        FROM deleted
        end
            IF EXISTS(SELECT 1 FROM INSERTED i inner join VERAENDERUNGEN_LOG gwa on gwa.NUMMER = XXX_NUMMER and i.XXX_TYP=3)
        BEGIN
        update gwa
        set gwa.INSERT_TIMSTP = getdate()
        from VERAENDERUNGEN_LOG gwa
        inner join INSERTED i on i.XXX_NUMMER = gwa.NUMMER
        and gwa.STATUS = 0
        END
    ELSE
        BEGIN
        INSERT INTO VERAENDERUNGEN_LOG
        SELECT XXX_NUMMER, 0, getdate()
        FROM INSERTED
        end

    END


    END


    Irina

    Montag, 8. Dezember 2014 13:19

Antworten

Alle Antworten

  • Hallo!
    Du weißt ja sicher, dass dein Trigger bei einem Update oder Insert von mehreren Zeilen fehl schlägt, oder?

    Weiterhin ist der Inhalt von exec proc_update_XXX_DATA_to_yyy unklar und damit die Auswirkungen auf die Daten, insbesondere die Sperren.

    Halte die Transaktionen im Trigger so kurz wie möglich.

    Auch solche Aktionen lassen sich in der Regel in einem Statement zusammenfassen

    if @XXX_typ = 3
        BEGIN
                IF EXISTS(SELECT 1 FROM deleted d inner join VERAENDERUNGEN_LOG gwa
    on gwa.NUMMER = d.XXX_NUMMER and d.XXX_TYP=3)
        BEGIN
        update gwa
        set gwa.INSERT_TIMSTP = getdate()
        from VERAENDERUNGEN_LOG gwa
        inner join deleted d on d.XXX_NUMMER = gwa.NUMMER
        and gwa.STATUS = 0
        ...

    Hier könntest Du so etwas probieren:

    update gwa
    set gwa.INSERT_TIMSTP = getdate()
    from VERAENDERUNGEN_LOG gwa
    inner join deleted d
    on d.XXX_NUMMER = gwa.NUMMER
    and gwa.STATUS = 0
    and d.XXX_TYP=3;

    INSERT INTO VERAENDERUNGEN_LOG(NUMMER, STATUS, INSERT_TIMSTP)
    SELECT XXX_NUMMER, 0, getdate()
    FROM deleted D
    Where not exists(Select * from VERAENDERUNGEN_LOG L where D.XXX_NUMMER = L.NUMMER)
    and d.XXX_TYP=3;

    update gwa
    set gwa.INSERT_TIMSTP = getdate()
    from VERAENDERUNGEN_LOG gwa
    inner join INSERTED i
    on i.XXX_NUMMER = gwa.NUMMER
    and gwa.STATUS = 0
    and i.XXX_TYP=3;

    INSERT INTO VERAENDERUNGEN_LOG(NUMMER, STATUS, INSERT_TIMSTP)
    SELECT XXX_NUMMER, 0, getdate()
    FROM INSERTED I
    Where not exists(Select * from VERAENDERUNGEN_LOG L where I.XXX_NUMMER = L.NUMMER)
    and I.XXX_TYP=3;

     Hat die Tabelle VERAENDERUNGEN_LOG einen Index auf NUMMER?

    Einen schönen Tag noch,
    Christoph
    --
    Microsoft SQL Server MVP - http://www.insidesql.org/blogs/cmu


    Montag, 8. Dezember 2014 14:15
  • Hallo Christoph,

    danke für Deine Antwort.

    Ich weiß dass der Trigger zeilenweise arbeitet. Wir lassen auch die Updates mehrerer Zeilen im Cursor laufen - dabei kommt es selten (gar nicht) zur Sperren. Die meisten Deadlocks ereignen sich wärend der Geschäftszeiten. VERAENDERUNGEN_LOG - hat einen index auf NUMMER.

    Meinst Du denn dass durch die Variable 

    <span style="color:blue">if</span> @XXX_typ = 3
    

    verursacht wird?


    Irina

    Montag, 8. Dezember 2014 16:01
  • Alternativ zu Triggern sollte man sich auch mal eine asynchrone Lösung mittels Event Notifications (CREATE EVENT NOTIFICATION), basierend auf Service Broker ansehen.

    Auch Change Data Capture könnte man in Betracht ziehen, wenn es um Verarbeitung von Änderungen geht.

    Ansonsten sind Christop's Hinweise absolut richtig und ernst zu nehmen.


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

    Montag, 8. Dezember 2014 21:20
  • Alle Aktionen im Trigger laufen in der Transaktion des auslösenden Statements.
    Um so mehr Schnörkel im Trigger sind, um so länger dauert er.
    Wenn Du durch dein IF 99% der Fälle ausschließen kannst, würde es die Verarbeitung unter Umständen beschleunigen.
    Wenn sowieso ein großer Teil die Bedingung erfüllt, solltest Du sie nur einmal beim Update/Insert in die Log-Tabelle prüfen.

    Du verarbeitest also die Daten außen mit einem Cursor. Laufen diese Updates alle in einer Transaktion, oder machst Du viele Transaktionen auf und beendest diese auch direkt wieder?

    Die Idee mit der asynchronen Verarbeitung von Andreas gefällt mir auch ganz gut. Dann würde man nur per Trigger ein Update-Datum setzen und ein asynchroner Prozess würde alle x Minuten die Änderungen protokollieren, die seit dem letzten Lauf durchgeführt wurden.
    Der Prozess könnte dann auch SET-basiert viele Sätze auf einmal ändern.

    Was passiert eigentlich in der aufgerufenen Prozedur? Dort vermute ich eher die Aufwände, die die Verarbeitungszeit in die Länge ziehen.
     Einen schönen Tag noch,
    Christoph
    --
    Microsoft SQL Server MVP - http://www.insidesql.org/blogs/cmu

    Dienstag, 9. Dezember 2014 07:23
  • hier ist die Prozedur:


    CREATE proc [dbo].[proc_update_xxx_data_to_yyy_order] (@add_typ int, @add_nummer INT)
     as
    BEGIN
    --SET NOCOUNT ON;


    declare @ans_typ nvarchar(8);
    declare @ans_nummer nvarchar(50);
    declare @invoicenr nvarchar(100);
    declare @sql nvarchar(max);
    declare @sql_1 nvarchar(max);


    set @ans_typ = case @add_typ
    when 1 then 'p'
    when 2 then 'f'
    when 3 then 'k'
    when 4 then 'l'
    end;
    set @ans_nummer = cast(@add_nummer as nvarchar(50));

    set @invoicenr = @ans_typ + @ans_nummer;

    set @sql= N'delete from yyyOrder.dbo.[Order]'+  
    N' where yyyOrder.dbo.[Order].InvoiceNr like @invoicenr' +
    N' or yyyOrder.dbo.[Order].InvoiceNr like replace(@invoicenr,SUBSTRING(@invoicenr,1,1),''dhl'')'


    ;

    set @sql_1 =
    N'INSERT INTO yyyOrder.dbo.[Order] '+

        N'( Product ,'+
        N'Shipper ,'+
        N'ShipFromAddress1 ,'+
        N'ShipFromAddress2,'+
        N'ShipFromStreet ,'+
        N'ShipFromHouseNr ,'+
        N'ShipFromPostalCode ,'+
        N'ShipFromCity ,'+
        N'ShipFromCountry ,'+
        N'ShipFromPhone ,'+
        N'ShipToAddress1 ,'+
        N'ShipToAddress2 ,'+
        N'ShipToAddress3 ,'+
        N'ShipToStreet ,'+
        N'ShipToPostalCode ,'+
        N'ShipToCity ,'+
        N'ShipToCountry ,'+
        N'ShipToPhone ,'+
        N'ShipToMail ,'+
        N'InvoiceNr ,'+
        N'PackageLength ,'+
        N'PackageWidth ,'+
        N'PackageHeight ,'+
        N'PackageDescription ,'+
        N'PackageWeight ,'+
        N'Created )'+
    N' select * from dbo.udf_OrderShippers_'+@ans_typ+ '('+@ans_nummer+')'
    ;
        

    begin

        EXECUTE sp_executesql @sql, N'@invoicenr nvarchar(100)', @invoicenr =@invoicenr;
        EXECUTE sp_executesql @sql_1;

    end

    end
       


    Irina


    Dienstag, 9. Dezember 2014 10:34
  • ...ein asynchroner Prozess ist für mich von grosser Interesse. Wir wünschen uns den ereignisgesteuerten Modell näher kommen

    Irina

    Dienstag, 9. Dezember 2014 10:38
  • Hallo Irina,
    jetzt überleg mal selber. Dynamisches SQL, eine weitere User Defined Function und das ganze evtl. auch noch über mehrere Datenbanken hinweg, wo entsprechende Berechtigungsprüfungen notwendig werden.

    Ich hoffe nicht, dass an der Tabelle yyyOrder.dbo.[Order] noch weitere Trigger hängen. Hat die Tabelle denn einen Index auf InvoiceNr ?

    Warum verwendest Du dort ein Like? like @invoicenr ...
    Warum diese Aufsplittung der Logik auf die verschiedenen Teile?
    Warum dynamisches SQL?

    Wenn ihr die Daten wirklich nicht sofort braucht, schreibt Euch nur ein Änderungsdatum weg und macht den Rest später.
     Einen schönen Tag noch,
    Christoph
    --
    Microsoft SQL Server MVP - http://www.insidesql.org/blogs/cmu

    Dienstag, 9. Dezember 2014 10:50
  • Hallo  Andreas, Change Data Capture  gibt es leider nicht, weil es ist ein SQL Server 2005.

    Die Service Broker Idee gefällt mir sehr gut 


    Irina

    Dienstag, 9. Dezember 2014 10:53
  • Hi Christoph,ich habe es so aufgeteilt um die Fehlermeldungen besser zu verfolgen und eben die Aktionen im Trigger minimal zu halten (habe nämlich mit Triggern nicht wirklich Erfahrungen). Ich habe beobeachtet dass seit ich den SELECT Befehl in eine Funktion ausführe - läuft das Ganze um mindestens 100-fache schneller.

    Auch wollten Wir die Funktion an verschiedenen anderen Stellen Verwenden. Prozedur ebenfalls.

    Auf InvoiceNr ist ein INDEX. InvoiceNr setzt sich aus verschiedenen Teilen zusammen, abhängig von der Bestellenden. Das ich mit den Triggern angefangen habe liegt daran dass wir den Versand "zeitecht" machen wollten, deswegen brauchen wir die Daten sofort.  Was wäre Deine Idee?

    Besonderes schlimm ist es während der Geschäftszeiten und eben und weil die Nachtjobs wegen Sperren nicht ausgeführt werden, manch mal.


    Irina

    Dienstag, 9. Dezember 2014 13:25
  • ...an der Order Tabelle sind keine weitere Trigger

    Irina

    Dienstag, 9. Dezember 2014 13:56
  • Das mit dem Like macht doch nur Sinn, wenn Du auch entsprechende Suchmasken mit % oder anderen Sonderzeichen bildest, oder?
    Das dynamische SQL ist meiner Meinung nach überflüssig und kostet unter Umständen nur noch Zeit.

    Neben meinen anderen Vorschlägen weiter oben im Thread, könnte man noch den Ansatz verfolgen mit der Zeilenversionsverwaltung die Sperren zu reduzieren.
    http://www.insidesql.org/blogs/cmu/sql_server/zeilenversionsverwaltung-row-level-versioning

    Musst Du wirklich alle Sachen aus dem Trigger und der Prozedur zeitnah machen, oder kannst Du einen Teil später (asynchron) erledigen?
     Einen schönen Tag noch,
    Christoph
    --
    Microsoft SQL Server MVP - http://www.insidesql.org/blogs/cmu

    Dienstag, 9. Dezember 2014 14:06
  • ..das mit den asynchronen Ansatz finde ich gut.

    Eine kleine Verzögerung von ein Paar sek. ist in Ordnung.

    Hast Du eine Idee, welche Event Notifications könnte ich denn für den Service Broker definieren?: Update und Insert? Soll dadurch die Prozedur ausgelöst werden, welche die Insert macht in die Order Tabelle?

    Und im Trigger werde ich einfach die eingefügten Zeilen in eine Zwischentabelle schreiben?

     


    Irina

    Dienstag, 9. Dezember 2014 15:11
  • Hallo Irina,

    Pardon, ich habe da etwas durcheinandergehauen. mit Event Notifications kann ich nur auf DDL-Events reagieren.

    Für DML gibt es keine "Events".

    Insofern halte ich ein kurzes Status-Update an der Zeile und nachfolgende Verarbeitung durch einen asynchronen Prozess, wie durch Christoph beschrieben, für am einfachsten.

    Wenn mehr Performance und Skalierbarkeit gefordert ist, kann man auch einen Trigger so implementieren, dass er die Änderungen (select * from inserted und deleted) als XML in eine Service-Broker Queue übergibt, die dort durch multiple threads verarbeitet werden können. Aber ich vermute fast das wäre hier zu viel.

    Ein Upgrade auf SQL Server 2014 lege ich auch nahe, denn der Support ist längst dahin und neue Features sparen auch durchaus Zeit bei Eigenentwicklungen.


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

    Dienstag, 9. Dezember 2014 18:34
  • Hallo Andreas, die Lösung mit dem Service Broker hat mir sehr gut gefallen. Warum sollte man es nicht benutzen, "wenn bereits im Packet liegt"? Allerdings habe ich auch nach einer intensive Recherche angefangen zu vermuten dass DML Ereinisse keine "Events" sind.

    Ich finde dass es hier auf keinen Fall zuviel ist. Meinst Du, man kann die Daten aus dem "deleted" und "inserted" im Form von Messages an Queue schicken?Warum soll das eigentlich XML format sein? Um einen ganzen Datensatz zu verketten?

    Hast Du eine Beispiel?


    Irina

    Mittwoch, 10. Dezember 2014 16:15
  • Hallo Christoph,

    ich habe Dein Beitrag gelesen. Stimmt das das die "row-level-versionierung" ein default Einstellung im SQL Server 2005 ist? Oder soll eine "SET" Option geben?

    Was wäre Dein Ratschlag, wie ich die spätere Verarbeitung steuern könnte: Eine LOG_Tabelle "unter" der Usertabelle implementieren und auch noch da ein Trigger?

    Viele Grüße und Danke


    Irina

    Mittwoch, 10. Dezember 2014 16:33
  • Hallo Irina.

    die Nachrichten, mit denen Service-Broker arbeitet sind nunmal auf XML (oder NULL) beschränkt.

    Es gibt da zwei mir bekannte Anstätze:

    1. Den Trigger als CLR-Trigger implementieren, der dieses XML in die entsprechende SB-Queue packt.
    2. Innerhalb eines T-SQL Triggers den Inhalt von inserted bzw deleted als XML verpacken und absenden.
      Das geht im Prinzip so:
    SELECT * FROM inserted FOR XML PATH('row'), ROOT('xyz') );
    Abgesehen von normalen Error-Handling ist das Verpacken und Absenden auch der einzige Inhalt dieses Triggers dann.

    Ab da gelten die normalen Service Broker Konzepte. In Books Online müsste ein einfaches Beispiel zu finden sein.

    Row-Versioning aka Read Committed Snapshot Isolation ist in keiner Version von SQL Server Standard. Der ist immer Read Committed. Trigger verwenden aber in der Tat im Hintergrund die selbe Technik um an die "alten Versionen der Zeilen" heranzukommen.


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

    Mittwoch, 10. Dezember 2014 16:44
  • Hallo Irina,
    wenn Du den Artikel gelesen hast, wirst Du sehen, dass es eine Datenbank-Option ist, die man einschalten muss. Wir haben recht gute Erfahrungen damit in einem Projekt gemacht. Aber das musst Du mal testen.

    Ich würde über einen Trigger nur minimale Informationen protokollieren und dann asynchron (z. b. per SQL Agent Job) die Daten dann weiter verarbeiten.

    Einen schönen Tag noch,
    Christoph
    --
    Microsoft SQL Server MVP - http://www.insidesql.org/blogs/cmu

    Donnerstag, 11. Dezember 2014 08:09
  • ... die Lösung mit dem Service Broker hat mir sehr gut gefallen.

    Hallo Irina,

    ich nutzte den Service Broker gerne, insbesondere für langlaufende Prozesse oder um Daten zwischen verschiedenen Instanzen/Datenbanken zu übertragen.

    Beispiel: Wir haben ein ERP-System, in dem Artikeldaten gepflegt werden. Aus den Daten werden PDF Dateien erzeugt und die in den Online-Shop übertragen und wie immer soll das "sofort" erfolgen und nicht irgendwann zeitgesteuert. Das direkt im Trigger zu machen (z.B. als CLR) wäre ein echter Performancekiller. Hier mache ich es so, das im Insert/Update Trigger der PK aller betroffene Datensätze zum Service Broker gesendet wird, über External Activator wird die Queue überwacht und per externem Programm dann die Daten exportiert, um daraus PDF's zu generieren. Works like a charm, selbst bei ein Update von ein paar Tausend Artikel merkt man Datenbankseitig keinen Unterschied, das ist gewohnt schnell durch. Das externe Programm rappelt dann eine Zeit lang bis die Tsd PDF's erzeugt sind, aber was soll's.

    Eine SB Lösung ist zunächst etwas Fleißarbeit, weil man die ganzen Contracts, Queues und Services definieren muss, aber wenn das erledigt ist, ist der Rest sehr einfach. Ich hatte mal ein kleines Beispiel mit interner Aktivierung, also starten einer Stored Procedure, erstellt: http://olafhelper.over-blog.de/article-26077104.html


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Donnerstag, 11. Dezember 2014 09:49
  • Hallo Christoph,

    seit heute morgen habe ich die Zeilenvesionsverwaltung eingestellt. Läuft bislang so als ob das Problem gelöst wäre.

    Eine interessante Beobachting habe ich gemacht, nachdem ich die Zwischenergebnisse in eine extra Tabelle schreiben lassen habe, ist eine kleine Trigger Debugging entstanden:

    Nach dem

    SET READ_COMMITTED_SNAPSHOT ON;
    SET ALLOW_SNAPSHOT_ISOLATION ON;

    kann man klar sehen dass Das Buchen eines Vorganges (ist hier natürlich teilweise Fachlich bedingt) erfolgt in 3 Zeilen: Insert mit DB_Lock = -1; Update_1 DB_Lock wird auf 0 geendert und Update_2 DB_Lock = 0 und Trigger führt die Prizedur aus (der DS wird dann in eine andere DB geschrieben und die Fehlende Eingaben (welche Benutzer vergessen hatte mit einzugeben) werden ergenzt)

    Vielen Dank und viele Grüße


    Irina

    Freitag, 12. Dezember 2014 12:18
  • Hier mache ich es so, das im Insert/Update Trigger der PK aller betroffene Datensätze zum Service Broker gesendet wird

    Hallo Olaf, wir haben auch viele Prozesse, welche etwas länger laufen. So etwas, wie verschiedene Echtzeit Auswertungen uber verschiedene Umsätze pro Artikel (welche im Webshop und Intranet abgebildet weeden) usw. Ich würde gerne SB dort anfangen einzusätzen.

    Dein Beispiel ist sehr anschaulich, aber ich habe nicht bemerkt, an welche Stelle wird ein ID (welche von dem rig kommt) in den Nachrich reingesschrieben.Vermutlich soll diese Abschnitt sich im Trigger befinden?

    Danke und Gruß


    Irina

    Freitag, 12. Dezember 2014 13:43