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?

  • 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 '17 at 21:33
  • (1) is worse than that, there are also 100 single-row updates on that secondary table. – Rick James Feb 1 '17 at 23:15
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.

  • 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 '17 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 '17 at 21:08
0

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.

  • (And the indexes already mentioned are needed.) – Rick James Feb 1 '17 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 '17 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 '17 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 '17 at 0:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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