SQL Server - AFTER INSERT and INSTEAD OF INSERT Trigger Performance

Implementing Oracle BEFORE INSERT Triggers in Microsoft SQL Server

Sometimes you have to use a trigger, not a default value to set the required column value. Consider a simple example:

SQL Server:

  IF OBJECT_ID('sales', 'U') IS NOT NULL
    DROP TABLE sales;
 
  CREATE TABLE sales
  (
     id INT PRIMARY KEY,
     created DATETIME DEFAULT GETDATE()
  );
  GO

Nothing prevents you from inserting any datetime (not the current datetime) to the created column:

  -- You can insert any datetime to created column
  INSERT INTO sales VALUES (1, '2010-09-17');

To enforce the constraint and insert the current datetime to the created column you can use a trigger.

Oracle BEFORE INSERT Trigger

In Oracle, you can use a row-level BEFORE INSERT trigger to set the actual current datetime no matter what value was inserted by a user:

Oracle:

  DROP TABLE sales;
 
  CREATE TABLE sales
  (
     id NUMBER(10) PRIMARY KEY,
     created DATE
  );
 
  -- Define a trigger
  CREATE OR REPLACE TRIGGER tr_sales
    BEFORE INSERT ON sales FOR EACH ROW
  BEGIN
    :new.created := SYSDATE;
  END;
  /

Now even if a user explicitly specifies a datetime, it will be overwritten to the current datetime by the trigger:

Oracle:

  INSERT INTO sales VALUES (1, TO_DATE('2010-09-17', 'YYYY-MM-DD'));
  -- Inserted row: 1, 2012-06-27 (current date)

SQL Server AFTER INSERT and INSTEAD OF INSERT Trigger

SQL Server does not provide BEFORE INSERT and FOR EACH ROW triggers, so you have to use either statement-level AFTER INSERT or INSTEAD OF INSERT trigger to set the current datetime.

SQL Server:

  IF OBJECT_ID('sales', 'U') IS NOT NULL
    DROP TABLE sales;
 
  CREATE TABLE sales
  (
     id INT PRIMARY KEY,
     created DATETIME
  );
  GO

AFTER INSERT Trigger

Unlike Oracle, where a row-level BEFORE INSERT trigger was used to set the column value, in SQL Server, you can use a statement-level AFTER INSERT trigger and update the column value after it has been inserted to the table.

So in case of AFTER INSERT trigger, two DML operations are performed: the rows firstly inserted to the table, and then they are updated.

A system inserted table can be used to identify which rows where inserted:

SQL Server:

   CREATE TRIGGER tr_sales ON sales
    AFTER INSERT
    AS
    BEGIN
      UPDATE sales
      SET created = GETDATE()
      FROM inserted
      WHERE sales.id = inserted.id;
    END
   GO

Now similar to Oracle BEFORE INSERT trigger, if a user explicitly specifies a datetime, it will be overwritten to the current datetime by the trigger:

SQL Server:

  INSERT INTO sales VALUES (1, '2010-09-17');
  -- Inserted row: 1, 2012-06-27 (current date)

INSTEAD OF INSERT Trigger

In SQL Server, you can also use a statement-level INSTEAD OF INSERT trigger to overwrite inserted values.

INSTEAD OF INSERT trigger requires one DML operation, you only need to modify values and insert rows, no subsequent update is required:

SQL Server:

  CREATE TRIGGER tr_sales ON sales
    INSTEAD OF INSERT
  AS
  BEGIN
   INSERT sales
   SELECT id, GETDATE()
   FROM inserted;
  END
  GO

This trigger also replaces any value inserted in the created column by a user with GETDATE() function:

SQL Server:

  INSERT INTO sales VALUES (1, '2010-09-17');
  -- Inserted row: 1, 2012-06-27 (current date)

Oracle BEFORE INSERT Trigger Performance

Firstly, let's see how the trigger affects the insert performance in Oracle.

Initial Insert Performance in Oracle

Let's measure the insert performance if you do not use a trigger, and use DEFAULT SYSDATE to set the current datetime to the created column:

Oracle:

  -- Table definition
  DROP TABLE sales;
 
  CREATE TABLE sales
  (
     id NUMBER(10) PRIMARY KEY,
     created DATE DEFAULT SYSDATE
  );

The following PL/SQL block inserts 100,000 rows issuing a commit after each 10,000 row:

Oracle:

  -- Run a loop to insert 100,000 rows
  DECLARE i NUMBER(10) := 1;
                 startd DATE := SYSDATE;
  BEGIN
    WHILE i <= 100000 LOOP
 
       INSERT INTO sales (id) VALUES (i);
       i := i + 1;
 
      -- Commit after each 10,000 row
      IF MOD(i, 10000) = 0 THEN
        COMMIT;
      END IF;
 
      END LOOP;
 
     -- Output the execution time in seconds 
    DBMS_OUTPUT.PUT_LINE((SYSDATE - startd)*24*60*60);
    END;
    /

This PL/SQL block was executed multiple times (dropping and recreating the table each time), and I got the following results:

Execution time Insert rate
42 seconds 2381 rows per second

Insert Performance with BEFORE INSERT Trigger in Oracle

Now let's define a trigger on the table (remove DEFAULT clause), and measure the insert performance:

Oracle:

  -- Table definition
  DROP TABLE sales;
 
  CREATE TABLE sales
  (
     id NUMBER(10) PRIMARY KEY,
     created DATE
  );
 
  -- Define a trigger
  CREATE OR REPLACE TRIGGER tr_sales
    BEFORE INSERT ON sales FOR EACH ROW
  BEGIN
    :new.created := SYSDATE;
  END;
  /

Using the same PL/SQL block that inserts 100,000 rows committing after each 10,000 row, I got the following results:

Execution time Insert rate
59 seconds 1695 rows per second

You can see that the row-level BEFORE INSERT trigger reduced the insert performance by 40% in Oracle.

SQL Server AFTER INSERT and INSTEAD OF INSERT Trigger Performance

Now let's see how the trigger affects the insert performance in SQL Server.

Initial Insert Performance in SQL Server

Firstly let's measure the insert performance if you do not use a trigger, and use DEFAULT GETDATE() to set the current datetime to the created column:

SQL Server:

  IF OBJECT_ID('sales', 'U') IS NOT NULL
     DROP TABLE sales;
 
  -- Table definition
  CREATE TABLE sales
  (
     id INT PRIMARY KEY,
     created DATETIME DEFAULT GETDATE()
  );
  GO

The following Transact-SQL block inserts 100,000 rows issuing a commit after each 10,000 row:

SQL Server:

  SET NOCOUNT ON
 
  -- Run a loop to insert 100,000 rows
  DECLARE @i INT = 1,
                  @start DATETIME = GETDATE();
 
  WHILE @i <= 100000 BEGIN
 
    -- Start a transaction
    IF @i % 10000 = 1 
       BEGIN TRANSACTION;
 
     INSERT INTO sales (id) VALUES (@i);
     SET @i = @i + 1;
 
    -- Commit after each 10,000 row
    IF @i % 10000 = 0
      COMMIT;
 
  END
 
  -- Output the execution time in seconds 
  SELECT DATEDIFF(ss, @start, GETDATE()); 
  GO

This Transact-SQL block was executed multiple times (dropping and recreating the table each time), and I got the following results:

Execution time Insert rate
8 seconds 12500 rows per second

Insert Performance with AFTER INSERT Trigger in SQL Server

Now we will define an AFTER INSERT trigger on the table (removing the DEFAULT clause), and measure the insert performance:

SQL Server:

  IF OBJECT_ID('sales', 'U') IS NOT NULL
     DROP TABLE sales;
 
  -- Table definition
  CREATE TABLE sales
  (
     id INT PRIMARY KEY,
     created DATETIME
  );
  GO
 
  -- Define a trigger
  CREATE TRIGGER tr_sales ON sales
    AFTER INSERT
    AS
    BEGIN
      UPDATE sales
      SET created = GETDATE()
      FROM inserted
      WHERE sales.id = inserted.id;
    END
   GO

Using the same Transact-SQL block that inserts 100,000 rows committing after each 10,000 row, I got the following results:

Execution time Insert rate
19 seconds 5263 rows per second

You can see that the statement-level AFTER INSERT trigger reduced the insert performance by 58% in SQL Server compared with using DEFAULT clause.

Insert Performance with INSTEAD OF INSERT Trigger in SQL Server

Now we will define an INSTEAD OF INSERT trigger on the table (removing the DEFAULT clause), and measure the insert performance again:

SQL Server:

  IF OBJECT_ID('sales', 'U') IS NOT NULL
     DROP TABLE sales;
 
  -- Table definition
  CREATE TABLE sales
  (
     id INT PRIMARY KEY,
     created DATETIME
  );
  GO
 
  -- Define a trigger
  CREATE TRIGGER tr_sales ON sales
    INSTEAD OF INSERT
  AS
  BEGIN
   INSERT sales
   SELECT id, GETDATE()
   FROM inserted;
  END
  GO

Now using the same Transact-SQL block that inserts 100,000 rows committing after each 10,000 row, I got the following results:

Execution time Insert rate
13 seconds 7692 rows per second

You can see that the statement-level INSTEAD OF INSERT trigger reduced the insert performance by 38% in SQL Server compared with using DEFAULT, but it is faster than AFTER INSERT trigger.

Test Results

We got the following results during the tests:

Database Execution Time (seconds) Insert Rate (rows per second)
Oracle DEFAULT Column 43 2381
Oracle BEFORE INSERT Trigger 59 1695
SQL Server DEFAULT Column 8 12500
SQL Server AFTER INSERT Trigger 19 5263
SQL Server INSTEAD OF INSERT Trigger 13 7692

Uptading Triggered Table - INSTEAD OF INSERT Is Faster Than AFTER INSERT Trigger

If you need a trigger to update values in the triggered table (the table on which the trigger is defined), INSTEAD OF INSERT trigger is faster than AFTER INSERT trigger due to the following reasons:

  • Before the trigger is fired, SQL Server inserts rows only to inserted table for INSTEAD OF INSERT trigger, and to inserted and the triggered table for AFTER INSERT trigger
  • AFTER INSERT trigger requires an additional UPDATE and a join with inserted table

System Information

Performance stats is always relative, and the following software/hardware was used for the tests in this article:

SQL Server:

  • Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
  • Express Edition with Advanced Services on Windows NT 6.1 <X86> (Build 7600:)

Oracle:

  • Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta

System:

  • Windows 7 Home Premium, 32-bit
  • CPU AMD E-350 1.6 GHz, dual core
  • RAM 3 GB, DDR3-1066
  • HDD - HITACHI HTS725032A9A364, 320 GB, 7200 rpm, SATA

Also note that all tests were ran multiple times and in different order to get stable results on the system.

Resources

Author

Dmitry Tolpeko, dmtolpeko@sqlines.com - July 2012

Discussion

, December 03, 2012 1:17 pm

good!! realy help to me know much information of trigger tansact sql, and please give more posting knowledge about trigger in sql server for example about function in if statment sql.

thanks

You could leave a comment if you were logged in.