0

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?

2
  • Two things: (1) Inserting 100 rows in two tables is very different from inserting 1 row in two tables 100 times... (2) is (tableA_id, valid_to) indexed?
    – joanolo
    Feb 1, 2017 at 21:33
  • (1) is worse than that, there are also 100 single-row updates on that secondary table.
    – Rick James
    Feb 1, 2017 at 23:15

2 Answers 2

2

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.

2
  • If this is a production system and you can't take out the insert or the update, then you could try adding a timestamp column to the actions to see when the inserts/updates occurred. Then swap the insert and the update to allow you to see the two timestamps and find which one takes longer.
    – CaM
    Feb 1, 2017 at 21:04
  • 1
    "Is there an index on the tableA_records.tableA_id column? There should be." The update was the issue as you suspected. Adding the index fixed it. Thank you very much!!!
    – dragmosh
    Feb 1, 2017 at 21:08
1

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.

4
  • (And the indexes already mentioned are needed.)
    – Rick James
    Feb 1, 2017 at 23:24
  • I thought the point of triggers was that it happens only when the event (here an update) occurs. Your tables seem to accomplish the same thing the trigger does, but how do you get it to happen whenever the row is updated? You mentioned client code, but wouldn't that miss when someone updates directly on the DB? Also, a trigger on the DB is done once per table, while putting it on the application level would be once per table per application, or setting up an API between the database and the applications.
    – dragmosh
    Feb 1, 2017 at 23:44
  • @dragmosh - Yes to everything you say. Your question asked about performance. I see my code as a good solution for performance. Granted, the issues in your comment still need to be addressed.
    – Rick James
    Feb 1, 2017 at 23:52
  • Ah I see what you mean. I should have been clearer in my question. I was only really looking for easy optimizations on how the trigger was defined. Your answer is helpful for people wanting to take the further steps though.
    – dragmosh
    Feb 2, 2017 at 0:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.