Trigger on Update

Publish on: Classify at: ;

On a Gantt chart of tasks we’ve been displaying a highly dynamic attribute next to the task. The attribute is dynamic in 2 ways:

  1. The information changes daily
  2. The requested information changes for particular tasks types on a quarterly to yearly basis. When the attribute was first introduced, it was changed almost weekly while we tested what information was most useful.

 

The original rendering was a scalar-valued function in the SQL query.

 

Uggh, I know.

 

The performance wasn’t awful, but I’m also not afraid to throw hardware at things.

 

Regardless, the scalar function has been replaced with a single column that is recomputed by a trigger on update.  This means anytime the central table to the Gantt chart is updated, the primary attribute column is recalculated based on the criteria placed in the trigger definition.  For each select query, the information is simply pulled from the column.  The trigger allows the information calculation steps to be centrally stored and only recalculated as needed.

For a few task types, the information that feeds into this attribute is impacted by tables other than the primary table.  In these instances, the trigger after update won’t capture most of the data changes.  In these cases, a periodic agent job to recalculate the column seems like a reasonable decision.  We can afford 15-minute old information in the column.

 

But what would this scenario cost?

One of the five pages where the Gantt chart renders had a SQL query with the scalar function in it.  This query takes 4 seconds to execute, which would happen each time the page is loaded (and on any server-side filtering changes thereafter).

By replacing the function with a column, the query takes 1 second.

Two of the pages with Gantt charts rendering were loaded over 150 times in 1 day across the organization – a minimum total time savings of 450 seconds of SQL compute time. Frankly, it’s much more likely triple that after page interactions.

 

If the agent job runs 4 times an hour for 10 hours of the day, that’s 80 seconds of SQL compute time.  With the trigger after update query cost almost indiscernible,  the shift away from the scalar function will be paying off immediately.

 

 

In case you’re wondering what that trigger after update ended up looking like:

 
[sql]
CREATE TRIGGER dbo.GDX_primaryAttribute
ON dbo.TeamGDx_WorkOrder
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;

UPDATE TeamGDx_WorkOrder
SET CustTxt3 = CASE WHEN WO.Type IN (1,2,7,9,10,12,14) THEN –LABOR RESOURCE/VENDOR
ISNULL(RTRIM(V.NAME),’No SP’)

  • CASE WHEN WO.TYPE IN (1,2,7) AND WO.AD3MODDATE IS NULL THEN ‘ – Packet Needed’ ELSE ” END
  • CASE WHEN ISNULL(WO.CUSTTXT2,”) <> ” THEN ‘ (‘+WO.CUSTTXT2+’ mi)’ ELSE ” END
  • CASE WHEN WO.TYPE = 7 AND ISNULL(S.moisture_testing,”)<>” THEN ‘(‘+S.moisture_testing+’)’ ELSE ” END
  • CASE WHEN WO.TYPE = 9 AND ISNULL(SL.response_text,”)<>” THEN ‘(‘+CASE WHEN SL.response_text = ‘Yes’ THEN ‘Plans Available’ WHEN SL.response_text = ‘Yes’ THEN ‘No Plans’ELSE ‘Plans Unknown’ END+’)’ ELSE ” END
    –LABOR COSTING OR LABOR REVIEW/USER
    WHEN WO.Type IN (3,6,15) THEN U.DISPLAYNAME
    –ESTIMATE/TEMPLATE
    WHEN WO.Type = 4 THEN ISNULL(SOX.DESCRIPTION,’No Template Assigned’)
    –JOB BID/BID TYPE
    WHEN WO.Type = 5 THEN ISNULL(S.IEbid_situation,”) + CASE WHEN ISNULL(S.IEbid_situation,”) <> ” THEN ‘ Bid, ‘ ELSE ” END
  • ‘Due ‘ + CONVERT(VARCHAR(10), E2.ACTIONDATE, 101)
    –MATERIALS/POX
    WHEN WO.Type = 8 THEN CASE WHEN WO.AD3MODDATE IS NULL THEN ” ELSE NCHAR(10004) END +
    P.DESCRIPTION + CASE WHEN P.PO_ID IS NULL OR WO.AD3MODBY IS NULL THEN ” ELSE
    case when po.status = ‘Q’ then ‘ (PQ ‘ else ‘ (PO ‘ end +CAST(P.PO_ID AS NVARCHAR(10))+’)’ END
  • ISNULL( (SELECT TOP 1 ‘ – ‘ +
    CASE WHEN SC.TRACK = 1 THEN SH.LS_MESSAGE ELSE SH.TRACK_CODE END
    FROM TEAMIT_SHIPMENTS SH WITH(NOLOCK) LEFT JOIN TeamIT_Shipments_Carriers SC WITH(NOLOCK) ON SH.CAR_CODE = SC.CAR_CODE
    WHERE SH.SHIP_CODE = P.POXID AND SH.ACTIVE = 1 ORDER BY SH.DELIVERY DESC)
    , ”)
    –EQUIPMENT/EQUIP
    WHEN WO.Type = 11 THEN RTRIM(ET.TYPEDESCR) + ‘-‘ + EQ.MODEL + ‘ ‘ + EQ.ASSETNUM
  • CASE WHEN SHIP.EQ_ID IS NOT NULL THEN ‘ – ‘ + SHIP.LS_MESSAGE ELSE ” END
    END
    FROM TeamGDx_WorkOrder WO
    INNER JOIN inserted I ON I.WOId = WO.WOId
    LEFT JOIN IECISAPP.dbo.VENDOR V ON WO.CUSTTXT1 = V.VENDID
    LEFT JOIN TEAMIT_SOW S WITH(NOLOCK) ON WO.PID = S.JOBCODE
    LEFT JOIN TeamIT_SOW_List SL WITH(NOLOCK) ON S.plans_available = SL.response_id AND SL.column_list = ‘plans_available’
    LEFT JOIN USERS U ON WO.SID = U.USERID
    LEFT JOIN TEAMIT_SOX SOX ON SOX.SOXID = WO.CUSTTXT1
    LEFT JOIN TeamGDx_WO_Event E2 WITH(NOLOCK) ON WO.WOId = E2.WOId AND E2.WOELID IN (SELECT WOELId FROM TeamGDx_WO_Event_List WITH(NOLOCK) WHERE CUSTINT1 = 2 AND ACTIVE = 1)
    LEFT JOIN TEAMIT_POX P WITH(NOLOCK) ON WO.AD3MODBY = P.POXID
    left join iecisapp.dbo.purchord po on p.po_id = po.ponbr
    LEFT JOIN TEAMEQ_EQUIPMENT EQ WITH(NOLOCK) ON WO.CUSTTXT1 = EQ.ID
    LEFT JOIN TEAMEQ_EQUIPMENTTYPE ET WITH(NOLOCK) ON ET.TYPEID = EQ.TYPE
    LEFT JOIN TEAMIT_SHIPMENTS SHIP WITH(NOLOCK) ON SHIP.EQ_ID = WO.WOID AND SHIP.ACTIVE = 1

END

[/sql]