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!

3 comments:

  1. Anonymous5:52 am

    When working with any type of internet search engine, it can help to comprehend how much time that hublot replica uk site has been around on the web, in addition to try acquiring more info relating to this. You'd probably come to an agreement that, generally, a substantial business may have referred to it is rolex replica sale strategy certainly. Take advantage of that just like a determine to be able to calculate the actual genuineness in the business you would like dealing with rolex replica. Each features being analyzed inside the pointed out strategy in the rolex replica sale business include the assure in addition to return strategy. You are feeling confident with the idea that you’ll are able to return the particular rolex replica look at in case you don’t appreciate it, as well as it isn't working properly. Just before closing the actual provide, you may make inquiries by way of e-mail, consequently sustaining a good store from all of the problems discussed while using merchant.

    ReplyDelete