Sql-server – Just update one column, trigger updates another

sql serversql-server-2016t-sqltrigger

I have logged the slowest queries on my database, and one surprises me, showing many times in the list, and taking often many seconds to execute.

UPDATE books SET last_read='2018-12-31 11:23:45' WHERE book_id='15'

book_id is int identity PK (clustered), last_read is a datetime. The query is written with the 15 in single quotes, thus requiring a conversion, but I cannot imagine this being a big deal, because the conversion would only be done once per query. There are 6 indexes on the table, but the column last_read is not involved or included in any of them. The PK is on book_id and is nothing special.
The table is very central in database, contains 50 columns and 400,000 rows.

The estimated execution plan tells me:

UPDATE: 0%
Clustered Index Update (on the PK constraint): 100%

The table has a trigger FOR DELETE, which I consider irrelevant here, and a trigger AFTER UPDATE, which reads:

CREATE TRIGGER books_last_update
ON books
AFTER UPDATE
AS
    UPDATE books
    SET last_update = GETDATE()
    WHERE book_id IN (SELECT book_id FROM Inserted)
GO

This shows me that the initial query actually performs two queries, one after the other. However, both of the updated columns have no index defined.
I would like to make the initial query perform faster.

Do you see any optimization possible?

I am afraid that, since the trigger is on UPDATE, and it actually updates the column, it may trigger multiple times. Is it possible? If yes, how do I prevent it?

Best Answer

book_id is int

Then your code should say:

WHERE book_id = 15

And not:

WHERE book_id='15'

This shouldn't affect performance in this case, but it is a best practice to delimit (or not) correctly. Non-Unicode strings and dates with single quotes, Unicode strings prefixed with N, binary and numerics without any quotes.

I am afraid that, since the trigger is on UPDATE, and it actually updates the column, it may trigger multiple times. Is it possible?

No, SQL Server is smart enough to not throw itself off a cliff. It recognizes that the update inside the trigger shouldn't recursively call the trigger in an infinite loop. You can prove this by running a server-side trace or an extended events session and you will see that the update inside the trigger only runs once.

You can add code to prevent an update of that column from triggering a second update (for example if someone had manually updated the last_update column, but that doesn't seem relevant here).

Do you see any optimization possible?

Generally, if you find that triggers are slowing down your code (I haven't seen any proof of that in this case), look to get rid of the triggers. I'm not sure what application code is updating this table directly, but if you can use a stored procedure to process all updates to the table, then you can add the update to this column there, instead of having to rely on the trigger. But I suspect if you are waiting 5 seconds for this update to happen, the trigger is NOT the problem.

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.

MySQL After Update Trigger apparently not running

My initial guess would be based on the trigger's flow. Why ?

According to the Book

MySQL Stored Procedure Programming

Chapter 11 Page 251 Paragraph 3 says the following:

The most significant difference between BEFORE and AFTER triggers is that in an AFTER you are not able to modify the values about to be inserted into or updated with the table in question -- the DML has executed, and it is too late to try to change what the DML is going to do.

From the this statement, this is what I understand: Since the DML is executed and the DML is still spoken of in the future imperfect tense (I am not an English teacher), the new values are really not available to be read in a SELECT since the trigger is not done.

In your case, you are doing an AFTER UPDATE on phpbb_rathermenacing.phpbb_bbdkp_memberlist. With the trigger being 99.99999999% done, you are attempting to do the following in the third clause of the IF statement:

        ((select min(m.member_rank_id) 
            from phpbb_bbdkp_memberlist m 
            WHERE m.phpbb_user_id = NEW.phpbb_user_id AND 
                  m.member_guild_id = NEW.member_guild_id AND 
                  m.member_id != NEW.member_id) >= new.member_rank_id OR 
        (select count(m.member_rank_id) 
           from phpbb_bbdkp_memberlist m 
          WHERE m.phpbb_user_id = NEW.phpbb_user_id AND 
                m.member_guild_id = NEW.member_guild_id AND 
                m.member_id != NEW.member_id) = 0)

You are asking for the count and state of the table phpbb_bbdkp_memberlist that has not yet been changed. Regardless of the Storage Engine, I am very sure you will never get a response because the trigger has to finish in order for the Storage Engine to even think about posting changes to disk.

I have two old posts where I discuss why a trigger is no place for business intelligence:

Aug 11, 2011 : Policies RE database triggers in well-designed applications?
Aug 15, 2011 : Performance of a Trigger vs Stored Procedure in MySQL

SUGGESTIONS

Make that trigger a stored procedure
Remove the AFTER UPDATE trigger
Run the stored procedure after the UPDATE in your application.

Best Answer

Related Solutions

Mysql – Disable trigger for just one table

MySQL After Update Trigger apparently not running

SUGGESTIONS

Related Question