Why does this trigger slow down updates so much

Question

I have a table. Lets call it table tableA. There's another table which keeps data about the history of tableA. Let's call that tableA_records.

On tableA, there's a trigger on update which looks similar to this:

CREATE TRIGGER upd_tableA AFTER UPDATE ON tableA
FOR EACH ROW
if @disable_triggers is null then
BEGIN
 SET @now = unix_timestamp(now());
 UPDATE tableA_records
 SET valid_to = @now
 WHERE valid_to IS NULL AND tableA_id = old.id;
 INSERT INTO tableA_records (
   tableA_id, column2, column3, ...., column27, 
   valid_from, valid_to
 ) VALUES (
   new.id, new.column2, new.column3, ...., new.column27, 
   @now, NULL
 );
 END;
end if

The issue I'm having is that the trigger slows down updates a lot. Without the trigger, an update on 100 rows of tableA takes 0.2 seconds. Inserting those 100 rows on tableA_records takes 0.25 seconds. The combined actions of the trigger (update tableA_records, insert into tableA_records) and the triggering action (update tableA) should take 0.65 seconds if we sum the times for each. With the trigger, it takes 35 seconds.

Anyone have an idea of what's causing the slowdown?

Answer 1

Your trigger performs two distinct actions.

1. It "end-dates" the old record in the tableA_records table.
2. It inserts a fresh copy of the new record into the tableA_records table.

Without seeing how the tableA_Records audit table is constructed, I can't be sure if one step is causing more problems than the other, but some questions you can ask and steps to try:

1. Is there an index on the tableA_records.tableA_id column? There should be.
2. What other indexes are on the tableA_records table? Are they necessary? Are they well designed?
3. Have you tried commenting out the update statement, then testing the trigger, then uncommenting that and commenting the insert statement and retesting? This will tell you if the update or the insert is slower.

I suspect you'll find that more of your delay is caused by the update than the insert, since an update requires scanning through either an index or a full-table scan to find the appropriate row. Make sure that column is properly indexed.

If other columns in tableA_records table are indexed, then look to see if that index is needed. Having too many indexes will slow down inserts.

Answer 2

This seems like a case where a TRIGGER is not the best way to solve a problem.

Instead, the client code should say (possible via a Stored Procedure):

CREATE TEMPORARY TABLE Staging
    SELECT ... the 100 rows that are destined for tableA;
BEGIN;
UPDATE tableA JOIN Staging ON ... SET tableA...;
UPDATE tableA_records JOIN Staging ON ... SET valid_to = NOW();
INSERT INTO tableA_records
    SELECT ... FROM Staging;
COMMIT;

By making it a single transaction, you get the atomicity you probably desire, plus you cut back on the overhead. By doing mass UPDATEs and INSERTs, the processing is a lot more efficient. By putting the CREAETE outside the transaction, you minimize the overhead in case you get a deadlock, or otherwise need to replay the transaction.