Extremely Slow Trigger Problem

I have an after update trigger that is killing me and I can't figure out why
.
When I run an update query that fires the trigger and look at the execution
plan, 85% of the cost is in the OPEN __0005 statement. I'm only updating 1
row, so this makes no sense to me. The I/O cost of opening the cursor is in
the 1100 range. Anyone got any ideas - I'm fresh out.
CREATE TRIGGER trgUpdate ON ITEMTASKS
FOR UPDATE
AS
DECLARE
@.ITE_ID INT,
@.TAS_ID INT,
@.STATUS TINYINT,
@.OLDSTATUS TINYINT
IF (@.@.ROWCOUNT = 0) RETURN
IF ( UPDATE( ITS_STATUS) ) BEGIN
SET NOCOUNT ON
DECLARE __0005 CURSOR LOCAL FAST_FORWARD FOR
SELECT I.ITE_ID, I.ITS_STATUS, I.TAS_ID, D.ITS_STATUS
FROM INSERTED I, DELETED D
WHERE I.ITE_ID=D.ITE_ID AND I.TAS_ID = D.TAS_ID
OPEN __0005
FETCH NEXT FROM __0005
INTO @.ITE_ID, @.STATUS, @.TAS_ID, @.OLDSTATUS
WHILE (@.@.FETCH_STATUS = 0 ) BEGIN
IF (@.STATUS = 6 ) BEGIN
UPDATE ITEMS
SET ITE_STATUS = 6
WHERE ITE_ID = @.ITE_ID
END
ELSE IF (@.STATUS = 2) AND ((SELECT TAS_ISLAST FROM TASKS WHERE TAS_ID =
@.TAS_ID)=1) BEGIN
UPDATE ITEMS
SET ITE_STATUS = 2
WHERE ITE_ID = @.ITE_ID
END
ELSE BEGIN
UPDATE ITEMS
SET ITE_STATUS = 1
WHERE ITE_ID = @.ITE_ID
END
EXEC ADD2SUMMARY_ @.ITE_ID, @.TAS_ID, @.STATUS, @.OLDSTATUS
FETCH NEXT FROM __0005
INTO @.ITE_ID, @.STATUS, @.TAS_ID, @.OLDSTATUS
END
CLOSE __0005
DEALLOCATE __0005
SET NOCOUNT OFF
ENDI strongly recommend you don't use cursors at all in triggers. Why
can't you re-write this and the contents of Add2Summary as set-based
SQL?
If you need more help then please come back with DDL, sample data
INSERT statements and show your required end result.
David Portas
SQL Server MVP
--|||Supposing that the cursor is necesary, I have two suggestions:
First -
Declare de cursor static and read_only
Second - Use EXIST in the following statement
-- ELSE IF (@.STATUS = 2) AND ((SELECT TAS_ISLAST FROM TASKS WHERE TAS_ID =
@.TAS_ID)=1) BEGIN
ELSE IF (@.STATUS = 2) AND exists(SELECT * FROM TASKS WHERE TAS_ID =
@.TAS_ID and TAS_ISLAST = 1) BEGIN
I do not know anything about the sp being executed, but the three updates
statements can be implemented without the use of the cursor.
AMB
"Arghknork" wrote:

> I have an after update trigger that is killing me and I can't figure out w
hy.
> When I run an update query that fires the trigger and look at the executi
on
> plan, 85% of the cost is in the OPEN __0005 statement. I'm only updating
1
> row, so this makes no sense to me. The I/O cost of opening the cursor is
in
> the 1100 range. Anyone got any ideas - I'm fresh out.
> CREATE TRIGGER trgUpdate ON ITEMTASKS
> FOR UPDATE
> AS
> DECLARE
> @.ITE_ID INT,
> @.TAS_ID INT,
> @.STATUS TINYINT,
> @.OLDSTATUS TINYINT
>
> IF (@.@.ROWCOUNT = 0) RETURN
> IF ( UPDATE( ITS_STATUS) ) BEGIN
> SET NOCOUNT ON
> DECLARE __0005 CURSOR LOCAL FAST_FORWARD FOR
> SELECT I.ITE_ID, I.ITS_STATUS, I.TAS_ID, D.ITS_STATUS
> FROM INSERTED I, DELETED D
> WHERE I.ITE_ID=D.ITE_ID AND I.TAS_ID = D.TAS_ID
> OPEN __0005
> FETCH NEXT FROM __0005
> INTO @.ITE_ID, @.STATUS, @.TAS_ID, @.OLDSTATUS
> WHILE (@.@.FETCH_STATUS = 0 ) BEGIN
> IF (@.STATUS = 6 ) BEGIN
> UPDATE ITEMS
> SET ITE_STATUS = 6
> WHERE ITE_ID = @.ITE_ID
> END
> ELSE IF (@.STATUS = 2) AND ((SELECT TAS_ISLAST FROM TASKS WHERE TAS_ID =
> @.TAS_ID)=1) BEGIN
> UPDATE ITEMS
> SET ITE_STATUS = 2
> WHERE ITE_ID = @.ITE_ID
> END
> ELSE BEGIN
> UPDATE ITEMS
> SET ITE_STATUS = 1
> WHERE ITE_ID = @.ITE_ID
> END
> EXEC ADD2SUMMARY_ @.ITE_ID, @.TAS_ID, @.STATUS, @.OLDSTATUS
> FETCH NEXT FROM __0005
> INTO @.ITE_ID, @.STATUS, @.TAS_ID, @.OLDSTATUS
> END
> CLOSE __0005
> DEALLOCATE __0005
> SET NOCOUNT OFF
> END
>|||Agreed, but I inherited the code and it's in production this way. It has
been working okay up until about a w ago, and I think there is an
underlying problem here I'm not seeing. Even with a cursor, it shouldn't
cost so much to open that cursor with 1 row in the inserted/deleted tables.
"David Portas" wrote:

> I strongly recommend you don't use cursors at all in triggers. Why
> can't you re-write this and the contents of Add2Summary as set-based
> SQL?
> If you need more help then please come back with DDL, sample data
> INSERT statements and show your required end result.
> --
> David Portas
> SQL Server MVP
> --
>