Sql-server – Efficiciency of multiple small triggers versus one large trigger on SQL Server

performancesql servertrigger

I have been given the honourable task of adding – very carefully! – a few triggers to our ERP system's database, to get data – "in real time" – from the ERP system to other pieces of software running at our company. The triggers will not make any changes to the data, just pick a few fields from the inserted/updates rows, roll them up in a XML message and send them to a message queue. There will be no JOINs, either.
(Whether or not this is a good idea in to begin with is a question for another time.)

I can imagine writing several triggers, one for each scenario. Or I could could write two large triggers, one for INSERT, and for UPDATE. With enough force, I could probably even boil that down to just one huge trigger that runs both on INSERT and on UPDATE.

From a testing/debugging/maintenance point of view, several self-contained triggers have obvious advantages: Each one can be written, tested and enabled/disabled individually. Each trigger would check if whatever just happened to the respective table is relevant to that trigger, if so, it would do its thing, and if not, it would just quit.

However, I am worried that multiple triggers might cause performance issues. The tables I am supposed to attach these trigger(s) see frequent INSERT and UPDATE queries as part of normal operation[1]. (OTOH, the load on the database server is relatively mild so far.)

Are there any general guidelines on which approach is better?

Thank you very much for any information you might be able to share,
Benjamin

[1] Frequent in this case means "a couple of dozen transactions per minute on a busy day".

Best Answer

_{Community wiki answer:}

You already sound like you know the right answer. The best approach is the one that has the trigger code run the fastest with the least amount of code. If it were me, I'd have one trigger per action. Will there be duplicate code - absolutely - but it saves you for when anything needs to be changed and can be customized per situation.

You could try both and then simulate load to see which you like/performs the best. Aaron Bertrand found there was observable additional overhead with multiple triggers performing the same operations, but YMMV.

However, a couple dozen transactions per minute doesn't sound like a tremendous amount of activity. Maintainability 'might' be able to justify a small amount of overhead. There are numerous advantages to using separate 'single-function' triggers. As you indicated, they have a single purpose and can be individually tested (by different people), enabled, disabled. Isolating the processing into separate 'single-function' triggers 'might' reduce the risk involved in future maintenance.

Of course, final testing of the individual triggers cannot be by different people, as you need to ensure the triggers work properly in unison. And, while future maintenance of smaller chunks of code is logically easier, you cannot ignore the other triggers when making future changes, as something that causes no issues in trigger A could completely mess up what trigger C is doing.

Related Solutions

Mysql – Disable trigger for just one table

Actually, if you place an if then block in every trigger, you could effectively shutdown all triggers. Here is such a code block

IF @TRIGGER_DISABLED = 0 THEN
    ...trigger body
END IF;

In the mysql environment, you could

run SET @TRIGGER_DISABLED = 1;
do your data maintenance
run SET @TRIGGER_DISABLED = 0;

So your trigger for table A should look like this:

BEGIN 
    IF @TRIGGER_DISABLED = 0 THEN
    IF (OLD.status != 1 AND NEW.status = 2) THEN 
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL) THEN 
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id; 
        END IF; 
    ELSEIF (OLD.Status = 1 AND NEW.Status != 2) THEN 
        IF (NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN 
            INSERT INTO geo (datatype, foreignid, long, lat, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status); 
        END IF; 
    ELSEIF (NEW.status != 3) THEN   
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL AND (NEW.geo_lat IS NULL OR NEW.geo_long IS NULL)) THEN 
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id; 
        ElSEIF ((OLD.geo_lat IS NULL OR OLD.geo_long IS NULL) AND NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN 
            INSERT INTO geo (datatype, foreignid, longitude, latitude, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status); 
        ELSEIF (OLD.geo_lat!=NEW.geo_lat OR OLD.geo_long != NEW.geo_long OR OLD.status != NEW.status) THEN 
            UPDATE geo SET lat = NEW.geo_lat, long = NEW.geo_long, status = NEW.status WHERE datatype IN (3,4) AND foreignid = NEW.id; 
        END IF; 
    END IF; 
    END IF; 
END

So your trigger for table B should look like this:

CREATE TRIGGER `usergroups_comments_insert` AFTER INSERT ON `usergroups_comment`     
    FOR EACH ROW     
    BEGIN     
        IF @TRIGGER_DISABLED = 0 THEN
            CALL sp-set-comment_count(NEW.`gid`);     
        END IF;
    END;

If you want the triggers for table A to launch but not to table B, then add the code block only to table B's trigger.

Sql-server – Performance of SQL Server Triggers

Your trigger runs after the updates have occurred on the table. If you update the table again in the trigger a new update is performed. See also the discussion about RECURSIVE_TRIGGERS.

If you want to run code before the update you will have to create an INSTEAD OF trigger.

Whether having to do two updates instead of one will become the bottleneck is entirely subject to the efficiency of the update in your trigger, and of course whether this UPDATE is already on the critical path or not. You probably have a primary key on job id, the pages are hot in buffer pool and you cannot have lock conflicts. So basically is only subject to a) how fast can your log accept writes and b) whether the version store (which serves the deleted and inserted pseudo-tables) can keep up.

If you ask me: such business logic status housekeeping belongs in the application, not in a trigger...

Best Answer

Related Solutions

Mysql – Disable trigger for just one table

Sql-server – Performance of SQL Server Triggers

Related Question