Início > SQL Server > Why Triggers are Bad

Why Triggers are Bad

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

Anúncios
Categorias:SQL Server
  1. Nenhum comentário ainda.
  1. No trackbacks yet.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: