Thursday, March 27, 2008

Trigger without a cursor example

From an example I wrote for someone at work.

ORIGINAL
CREATE TRIGGER S0787_RegionCodes_Insert ON RegionCodes
FOR INSERT
AS
DECLARE @rgnCode varChar(5)
DECLARE InsertedCursor CURSOR FOR
select rgnCode from Inserted
OPEN InsertedCursor
FETCH NEXT FROM InsertedCursor INTO @rgnCode
WHILE @@FETCH_STATUS=0
begin
SET NOCOUNT ON;
Insert InTo S0787_POExportConfig (rgnCode) values (@rgnCode);
Insert InTo S0787_POExportBatchGen (rgnCode) values (@rgnCode);
SET NOCOUNT OFF;
end
FETCH NEXT FROM InsertedCursor INTO @rgnCode
CLOSE InsertedCursor
DEALLOCATE InsertedCursor
GO

This can be rewritten in the following way

NEW
CREATE TRIGGER S0787_RegionCodes_Insert ON RegionCodes
FOR INSERT
AS
SET NOCOUNT ON;
INSERT INTO S0787_POExportConfig (rgnCode)
SELECT rgnCode FROM Inserted
WHERE rgnCode <> ''

INSERT INTO S0787_POExportBatchGen (rgnCode)
SELECT rgnCode FROM Inserted
WHERE rgnCode <> ''
GO

On a single row insertion, the original trigger (according to Profiler) made 285 reads and had a duration of 156. The new trigger made 10 reads and had a duration of 16.

This is orders of magnitude faster.

Don't use cursors, when you don't have to!

0 comments:

Post a Comment