4

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.

  • cannot calculate it on the spreadsheet or while you import the data? – McNets Apr 26 '17 at 22:48
  • 2
    Why not try it and get back to us? My immediate thought is - yes, as you will avoid lock contention. Computed columns in SQL Server can be persisted or not persisted too, likely you will not need to persist these columns unless you are indexing them. – blobbles Apr 26 '17 at 22:48
  • @blobbles, I would persist the columns if they are going to be used in where or join clauses; as well as if they are going to be indexed. I typically persist computed columns by default because storage is cheap and cpu is expensive. Yes, it takes up space in memory, but the increase is usually so small as to be negligible. – Jonathan Fite Apr 27 '17 at 1:10
  • Of course, but it is unlikely you join on amounts, as in the example provided. – blobbles Apr 27 '17 at 2:51
6

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.

enter image description here

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.