Slow T-SQL trigger

I have 2 tables "Vector" and "VectorElement". A vector has many elements so there is a foreign key relation between Vector and VectorElement with a cascaded delete.

Vector has a field V开发者_运维百科ectorSize that contains the number the number of related records in VectorElement.

Obviously this field is redundant but it optimizes performance and keeps our queries simple as we are oftyen interested in the number of elements in a vector.

There is a trigger on VectorElement that updates the VectorSize field in Vector. This trigger works but gets very slow when many Vector records are deleted or inserted in one transaction.

When the Vectors get deleted, the cascaded delete deletes the VectorElements after which the trigger fires. Now the trigger does update the to-be-deleted Vector record which could cause some trouble but this also happens with inserts.

Here is the trigger:

CREATE TRIGGER [TFact].[AfterDeleteInsertVectorElement] 
   ON  [TFact].[VectorElement] 
   AFTER DELETE, INSERT
AS 
BEGIN
    SET NOCOUNT ON;

  WITH cteChangedVectors AS
  ( 
      SELECT DISTINCT i.VectorId 
      FROM inserted i
      UNION 
      SELECT DISTINCT i.VectorId 
      FROM deleted i
  )

    UPDATE 
        TFact.Vector
    SET 
        VectorSize = x.size
    FROM 
        Vector v
    JOIN
        (SELECT VectorId, COUNT(*) as size FROM TFact.VectorElement GROUP BY VectorId) x
        ON v.Id = x.VectorId
    JOIN cteChangedVectors chg ON chg.VectorId = v.Id

END

Try tracking the total number of VectorElements using an indexed view.

See http://technet.microsoft.com/en-us/library/cc917715.aspx#XSLTsection124121120120

SQL Server knows how to track aggregates efficiently - that's cheaper than starting a piece of general purpose procedural code with every trigger call.

If you are on the SQL Server Enterprise, just create the view and your queries will be dynamically rewritten to use them.

Something like...

CREATE VIEW VectorSize AS
SELECT VectorId, COUNT(*)
FROM Vector NATURAL JOIN VectorElement
GROUP BY VectorId
GO
CREATE UNIQUE CLUSTERED INDEX VectorSizeInd ON VectorSize( VectorId )

SQL Server will then keep an automatically updated "hard-copy" of the vector sizes in the database.

The SQL looks over complex. And if you expect large sets, treat then separately

IF EXISTS (SELECT * FROM DELETED)
    UPDATE 
        V
    SET 
        VectorSize = x.size
    FROM 
        Vector V
        JOIN
        (SELECT
             VectorId, COUNT(*) as size
        FROM
             DELETED
        GROUP BY
             VectorId
         ) x
        ON v.Id = x.VectorId
ELSE
    UPDATE 
        V
    SET 
        VectorSize = x.size
    FROM 
        Vector V
        JOIN
        (SELECT
             VectorId, COUNT(*) as size
        FROM
             INSERTED
        GROUP BY
             VectorId
         ) x
        ON v.Id = x.VectorId

Is this any better?

UPDATE TFact.Vector
SET    VectorSize = x.size
FROM   Vector v
       inner join (
         select VectorId, count(*) size
         from   TFact.VectorElement
         where  VectorId in (select VectorId from cteChangedVectors)
         group by VectorId
       )x on x.VectorId = v.Id

Also make sure you have an index on TFact.VectorElement.VectorId if the DB gest big.

Regards GJ

Why not compute the "Delta" value from the inserted and deleted tables, rather than recompute the whole sum over the child table?

 UPDATE
     V
SET
     VectorSize = VectorSize + Delta
FROM
     Vector V
         inner join
     (select VectorID,SUM(Deltas) as Delta from
          (select VectorID,1 as Deltas from inserted union all
           select VectorID,-1 from deleted) t
     group by VectorID
     ) u
         on
            V.VectorID = u.VectorID