Sql-server – Calculated Column Or Trigger

performanceperformance-tuningsql serversql-server-2008-r2t-sql

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.

Best Answer

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.

Related Solutions

Sql-server – Trigger in combination with transaction

An insert is always within a transaction.

If you don't have an explicit BEGIN TRAN ... COMMIT or SET IMPLICIT_TRANSACTIONS ON then the statement runs as a self contained auto commit transaction.

The trigger is always part of the transaction for the action that fires the trigger. If an error occurs in the trigger that causes transaction rollback then the firing action will be rolled back too.

Triggers implicitly have XACT_ABORT on. An error with this setting on will automatically lead to transaction rollback (except for errors raised in the code with the RAISERROR statement).

Sql-server – Query to remove duplicate calculated values

Converting my comment as answer (SQLFiddle):

;with cte (cityID, label, rowNum, stateAbbrev, stateName)
as (
    SELECT 
    TOP 100 City.id AS cityID,
    City.name + ', ' + [State].[State]  as label,
    row_number() over (partition by cast(City.name + ', ' + [State].[State] as varchar(max)) order by City.id desc) AS rowNum, -- if you want the highest cityID then use "desc" else remove the "desc"
    [State].[State] AS stateAbbrev,
    [State].StateFullName AS stateName
  FROM [City]
  LEFT JOIN [Country] ON(City.countyID = Country.id)
  LEFT JOIN [State] ON(Country.stateID = [State].id)
  WHERE City.name + ', ' + [State].[State] LIKE 'Por%'
  order by City.id)  -- I have put in order by 
select cityID, label, stateAbbrev, stateName
        from cte
        where rowNum = 1

The above will result :

(click here to enlarge)

Note: As Aaron commented, you should remove CityID if not required in output list.

Best Answer

Related Solutions

Sql-server – Trigger in combination with transaction

Sql-server – Query to remove duplicate calculated values

Related Question