Calculated Column Or Trigger

Question

We currently have an oninsert() trigger set-up that everytime a row is insert into a table an update query fires off to update 3 fields. The fields are a simple calculation such as

field1update = (amt/12)*14
field2update = (amt/12)*16
field3update = (amt/12)*18

The trigger is adequate, but it will sometimes lock the row so it can not be immediately accessed for a moment. My question being if these fields were converted to a (please excuse my ignorance) calculated column or a computed column would we see performance improvement?

Usually this is a spreadsheet import and is roughly 20,000 - 25,000 rows being inserted at a time.

Answer 1

Based on the information you provided I did a very basic test and I suggest you set up something similar before you make a final decision. My result shows computed column will perform better over trigger but I want to stress that it might vary with your table structure, insert rate, other activity in the table and you need to test.

Set up:

CREATE TABLE TestTrigger
(
    amt INT NOT NULL,
    field1update INT,
    field2update INT,
    field3update INT
 )
 GO

CREATE TRIGGER triTestTrigger on TestTrigger
INSTEAD OF INSERT
AS
BEGIN
  INSERT INTO TestTrigger
       SELECT amt, (amt/12)*14, (amt/12)*16,(amt/12)*18
       FROM inserted
END
GO

CREATE TABLE TestCompCol
(
    amt INT NOT NULL,
    field1update AS (amt/12)*14,
    field2update AS (amt/12)*16,
    field3update AS (amt/12)*18
 )
 GO

Inserting into both table.

 INSERT INTO TestTrigger 
 (amt)
 VALUES(1)
 GO
 INSERT INTO TestCompCol
 (amt)
 VALUES(1)
 GO

First(trigger) insert has to do more work(70%) compare to second (computed column) insert(30%). If I look at the subtree cost for first insert (.010023+.0132842)=0.0233072 and for 2nd insert it is .0100022.

For cpu time of trigger insert I get

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 90 ms.

For computed column insert I get:

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 2 ms.