Announcement

Collapse
No announcement yet.

Huge performance impact of triggers on insert/update/delete operations

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Huge performance impact of triggers on insert/update/delete operations

    I have fileA which has 250k records. When batch job runs it processes this file instantly (all 250k records in less than a minute). Now when I add the trigger for insert/update/delete, it becomes 2-3 hours.

    The trigger has the following. What it does is it checks if a record exist on fileB via `Select 1 From FileB Where Field1=N.Field1`, it then it runs an if else sqlcode=100 then do either an update or an insert. All on the trigger. I tried to have it explain when I do each individual queries. I thought it was just an index problem. I created an index but when I explain the query, the index is not even used. Now I'm not sure what's causing the extreme slowdown.

    Code:
    CREATE TRIGGER library1.CrtFILE1
            AFTER INSERT OR DELETE OR UPDATE ON library1.FILE1
            REFERENCING OLD AS O NEW AS N FOR EACH ROW MODE DB2ROW
            SET OPTION ALWBLK = *ALLREAD,
                       ALWCPYDTA = *Optimize,
                       Commit = *None,
                       DBGVIEW = *Source,
                       DECRESULT = (31,
                       31,
                       00),
                       DFTRDBCOL = library2,
                       DYNDFTCOL = *No,
                       DYNUSRPRF = *User,
                       SRTSEQ = *HEX
        BEGIN ATOMIC
            /* Define Variable */
            DECLARE Sqlcode INTEGER DEFAULT 0;
            /* Inserting a new row */
            IF INSERTING THEN
                SELECT 1
                    FROM library2.FILE2
                    WHERE library2.FILE2.Field1 = N.Field1;
                IF Sqlcode = 100 THEN /* No other process has added a row */
                    INSERT INTO library2.FILE2 (
                                Field1,
                                Field2,
                                Field3,
                                Field4,
                                Field5,
                                Field6
                            )
                        VALUES
                            (
                                N.Field1,
                                'FILE1',
                                'INS',
                                Current_Timestamp,
                                '   ',
                                Current_Timestamp
                            );
                ELSE /* Row exists, need to update with the latest information */
                UPDATE library2.FILE2
                    SET (
                            Field1,
                            Field2,
                            Field3,
                            Field4,
                            Field5,
                            Field6
                        ) = (N.Field1, 'FILE1', 'INS', Current_Timestamp, '   ', Current_Timestamp)
                    WHERE library2.FILE2.Field1 = N.Field1;
                END IF;
            END IF;
            /*Updating a record in the Email Table */
            IF UPDATING THEN
                SELECT 1
                    FROM library2.FILE2
                    WHERE library2.FILE2.Field1 = N.Field1;
                IF Sqlcode = 100 THEN /* No other process has added a row */
                    INSERT INTO library2.FILE2 (
                                Field1,
                                Field2,
                                Field3,
                                Field4,
                                Field5,
                                Field6
                            )
                        VALUES
                            (
                                N.Field1,
                                'FILE1',
                                'UPD',
                                Current_Timestamp,
                                '   ',
                                Current_Timestamp
                            );
                ELSE /* Row exists, need to update with the latest information */
                UPDATE library2.FILE2
                    SET (
                            Field1,
                            Field2,
                            Field3,
                            Field4,
                            Field5,
                            Field6
                        ) = (N.Field1, 'FILE1', 'UPD', Current_Timestamp, '   ', Current_Timestamp)
                    WHERE library2.FILE2.Field1 = N.Field1;
                END IF;
            END IF;
            /* Deleting a record in the Email Table */
            IF DELETING THEN
                SELECT 1
                    FROM library2.FILE2
                    WHERE library2.FILE2.Field1 = O.Field1;
                IF Sqlcode = 100 THEN /* No other process has added a row */
                    INSERT INTO library2.FILE2 (
                                Field1,
                                Field2,
                                Field3,
                                Field4,
                                Field5,
                                Field6
                            )
                        VALUES
                            (
                                O.Field1,
                                'FILE1',
                                'DEL',
                                Current_Timestamp,
                                '   ',
                                Current_Timestamp
                            );
                ELSE /* Row exists, need to update with the latest information */
                UPDATE library2.FILE2
                    SET (
                            Field1,
                            Field2,
                            Field3,
                            Field4,
                            Field5,
                            Field6
                        ) = (O.Field1, 'FILE1', 'DEL', Current_Timestamp, '   ', Current_Timestamp)
                    WHERE library2.FILE2.Field1 = O.Field1;
                END IF;
            END IF;
        END;
    
    ;
    EDIT: I'm considering to convert this trigger a pure RPGLE program instead. And chain+update+unlock when needed. I imagine the program is executing this trigger 250k times and that is causing the slowdown.

    EDIT2: Added if else conditions on the trigger update statement to filter out update without value change from a chain->update rpgle sequence. CPU usage seconds went down by 90%. The remaining time I would guess is just DB2 trigger tax.

  • #2
    Hey, Old man, I think you're right. Running that trigger 250K times could slow the process down considerably.

    It seems to me you need to rethink your approach. If your goal is to change FILEB according to what's in FILEA, why not do it all at once instead of one row at a time thru a trigger? The MERGE command can update, insert, and delete rows in FILEB based on what's in FILEA.

    Comment


    • #3
      TedHolt , oh it runs on all that data, multiple times a day... 250k again and again and again...

      So the reason for having this trigger is that there's an API we call to send these over when there's an insert or update involved. Like an item master file of sorts that is being synced to another server somewhere in the world via APIs. The programs for those look into FileB. So FileB just holds what process was done on what item and when so the program knows what API to call.

      The program that updates File1, does so via RPGLE chain, so it's row by row. Would a trigger by statement work then? Not sure if waits for the program to end then run the trigger. or it runs the merge into per row. Another I wanted to check is old and new values since we need to know which specific field changed and not overload the API with nonsense. If I do that (I could be wrong here), the trigger, even when set by statement, will process by row since I now used O.Field1 <> O.FIeld2

      Comment

      Working...
      X