Blog – Fabiano Neves Amorim

I’m talking with Conor about triggers, I try explain why I don’t like triggers, and how they can be bad for performance. He ask-me one sample about bad performance then I create this script on database AdventureWorks, I think he will write in your blog about triggers soon, then keep eye.

 

If you don’t know him, he blog on SQLSkills, do you need more? J

 

http://www.sqlskills.com/blogs/conor/default.aspx

 

 

 

IF OBJECT_ID(‘Production.uProduct’) IS NOT NULL

  DROP TRIGGER Production.uProduct

GO

 

CREATE TRIGGER [Production].[uProduct] ON [Production].[Product]

AFTER UPDATE NOT FOR REPLICATION AS

BEGIN

    SET NOCOUNT ON;

 

    UPDATE [Production].[Product]

    SET [Production].[Product].[ModifiedDate] = GETDATE()

    FROM inserted

    WHERE inserted.[ProductID] = [Production].[Product].[ProductID];

END;

GO

 

— Here we have one column called ModifiedDate, and one trigger to update this column.

SELECT ModifiedDate, *

  FROM Production.Product

 WHERE ProductID = 316

 

— Now if I update the column Name of the product, the trigger will update the column

— ModifiedDate with the GetDate()

SET STATISTICS PROFILE ON

SET STATISTICS IO ON

 

UPDATE Production.Product

   SET Name = ‘New Blade’

 WHERE ProductID = 316

–Table ‘Product’. Scan count 0, logical reads 6, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

–Table ‘Product’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

SET STATISTICS IO OFF

SET STATISTICS PROFILE OFF

— Whats happened here? First the Update will search for the clustered Index,

— then the Trigger will do the same thing again. On the other hands,

— 2 scans on table Product

 

 

IF OBJECT_ID(‘Supper_UpdateProduct’) IS NOT NULL

  DROP PROC dbo.Supper_UpdateProduct

GO

— Now lets go create one Stored Procedure to Update the Name and ModifiedDate of the Product

CREATE PROC dbo.Supper_UpdateProduct (@NewName nVarChar(50) = ”, @ProductID Int = 0)

AS

BEGIN

  IF @ProductID = 0

  BEGIN

    UPDATE Production.Product

       SET Name = NewID(),

           ModifiedDate = GetDate()

  END

  ELSE

  BEGIN

    UPDATE Production.Product

       SET Name = @NewName,

           ModifiedDate = GetDate()

     WHERE ProductID = @ProductID

  END

END

GO

 

DISABLE TRIGGER Production.uProduct ON Production.Product;

GO

 

SET STATISTICS PROFILE ON

SET STATISTICS IO ON

 

EXEC dbo.Supper_UpdateProduct @NewName = N’My New Blade’, @ProductID = 316

–Table ‘Product’. Scan count 0, logical reads 6, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

SET STATISTICS IO OFF

SET STATISTICS PROFILE OFF

GO

— And now whats happened? The update search for the clustered index only one time.

 

ENABLE TRIGGER Production.uProduct ON Production.Product;

GO

 

 

 

— Now imagine one worst case,

BEGIN TRAN

GO

SET STATISTICS IO ON

GO

 

ENABLE TRIGGER Production.uProduct ON Production.Product;

GO

UPDATE Production.Product

   SET Name = NEWID()

GO

/*

Table ‘Product’. Scan count 1, logical reads 2075, physical reads 4, read-ahead reads 26, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘Product’. Scan count 1, logical reads 1052, physical reads 1, read-ahead reads 6, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

*/

 

DISABLE TRIGGER Production.uProduct ON Production.Product;

GO

EXEC dbo.Supper_UpdateProduct

GO

/*

Table ‘Product’. Scan count 1, logical reads 2078, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

*/

ROLLBACK TRAN