Sep 6, 2015

Use of trigger SQL Server Query Performance good or bad

Avoid using DML trigger at inappropriate place

I am not going to tell triggers are bad but there are many situations where using of triggers degrade the performance.  For example, sometimes there are triggers which reference same time and perform the same task which could be done the script which fire it.  In that case there is no need to create triggers. Let me explain it by an example:

Approach 1: Using trigger

a. Creating a table:
CREATE TABLE tblOrder(
     OrderID BIGINT PRIMARY KEY IDENTITY,
     OrderType INT,
     Qty INT,
     InsertDate DATETIME
)

b. Creating a trigger to update the insertion date:

CREATE TRIGGER Trg_Insert_Date ON tblOrder
AFTER INSERT
AS
BEGIN
    
    UPDATE  tblOrder
    SET     InsertDate = GETDATE()
    FROM    tblOrder O
    INNER JOIN inserted I
    ON     O.OrderID = I.OrderID

END

c. Populating records in the order table and enabling statistics:

SET STATISTICS IO ON

INSERT INTO tblOrder(OrderType,Qty)
SELECT message_id, severity FROM Sys.messages

Total logical read:

Table 'tblOrder'. Scan count 0, logical reads 645023
Table 'Worktable'. Scan count 1, logical reads 585437
Table 'tblOrder'. Scan count 1, logical reads 1036

Approach 2: Using Default value

a. Dropping trigger and creating equivalent default constraint on InsertDate column:

DROP TRIGGER Trg_Insert_Date

ALTER TABLE tblOrder
ADD CONSTRAINT [def_insert_dt]
DEFAULT (GETDATE())
FOR InsertDate

b. Removing all records from table:

TRUNCATE TABLE tblOrder

c. Populating records once again:

SET STATISTICS IO ON

INSERT INTO tblOrder(OrderType,Qty)
SELECT message_id, severity FROM Sys.messages

Table 'tblOrder'. Scan count 0, logical reads 645023
Table 'Worktable'. Scan count 1, logical reads 585437

After comparing logical reads we can say there are 1036 extra logical reads from tblOrder if we use trigger. Hence in this particular example it is bad approach to use trigger.

No comments:

Post a Comment