Trigger performance

[Scott CM]

I have a multi-part question regarding trigger performance. First of
all, is there performance gain from issuing the following within a
trigger:

SELECT PrimaryKeyColum n FROM INSERTED

opposed to:

SELECT * FROM INSERTED

Secondly, what is the optimum way to determine which action fired an
AFTER trigger that is defined for INSERT, UPDATE, DELETE? Is it
something similiar to:

IF NOT EXISTS(SELECT * FROM DELETED)
--sql block for insert
ELSE
IF NOT EXISTS(SELECT * FROM INSERTED)
--sql block for delete
ELSE
--sql block for update

or is there a superior method?

Lastly, is checking @@ROWCOUNT at the beginning of an AFTER trigger the
best way to differentiate between multiple rows being affected or just
a single row? Can using this @@ROWCOUNT test fail? Are there any
situations where it would return erroneous results?

I realize I'm being somewhat nitpicky on these matters but any feedback
would be greatly appreciated!

[David Portas]

Re: Trigger performance

1.[color=blue]
> SELECT PrimaryKeyColum n FROM INSERTED
>
> SELECT * FROM INSERTED[/color]

Since those two statements presumably return different results (unless you
only have one colum) I'm not sure what you are trying to compare here. It's
good practice to avoid SELECT * except in an EXISTS subquery. Always list
the columns you require because it makes your code clearer and more
maintainable and it maximizes the chances for the optimizer to use indexes.
In an EXISTS subquery the opposite argument applies: *any* index may be used
to optimize an EXISTS subquery so listing the column names is unnecessary
and makes no difference to the logical meaning of the query.

2.[color=blue]
> Secondly, what is the optimum way to determine which action fired an
> AFTER trigger that is defined for INSERT, UPDATE, DELETE?[/color]

Try to keep trigger logic simple and set-based - you may be able to write a
single DML statement rather than use multiple IFs in one trigger. If the
logic is quite different in each case then create separate triggers for each
action.

3.[color=blue]
> Lastly, is checking @@ROWCOUNT at the beginning of an AFTER trigger the
> best way to differentiate between multiple rows being affected or just[/color]

Why would you care if one or multiple rows were updated? Triggers should use
set-based code so the logic should be identical in any case. Never assume
that only single rows will be updated - this is a classic error and one that
causes a lot of grief judging by posts in this group.

--
David Portas
SQL Server MVP
--

[oj]

Re: Trigger performance

1. yes. 'select keycol' returns much less data. thus, it's more efficient.
2. yes. you want to use if...then block for each type.

e.g.
declare @i int, @d int
select @i=count(*) from inserted
select @d=count(*) from deleted

if @i=@d
print 'update'
if @i>@d
print 'insert'
if @i<@d
print 'deleted'

3. yes. @@rowcount in after trigger would tell us if any rows have been
affected. 0 will be returned if no row is affected.


"Scott CM" <scurriem@hotma il.com> wrote in message
news:1102719305 .071504.141400@ z14g2000cwz.goo glegroups.com.. .[color=blue]
>I have a multi-part question regarding trigger performance. First of
> all, is there performance gain from issuing the following within a
> trigger:
>
> SELECT PrimaryKeyColum n FROM INSERTED
>
> opposed to:
>
> SELECT * FROM INSERTED
>
> Secondly, what is the optimum way to determine which action fired an
> AFTER trigger that is defined for INSERT, UPDATE, DELETE? Is it
> something similiar to:
>
> IF NOT EXISTS(SELECT * FROM DELETED)
> --sql block for insert
> ELSE
> IF NOT EXISTS(SELECT * FROM INSERTED)
> --sql block for delete
> ELSE
> --sql block for update
>
> or is there a superior method?
>
> Lastly, is checking @@ROWCOUNT at the beginning of an AFTER trigger the
> best way to differentiate between multiple rows being affected or just
> a single row? Can using this @@ROWCOUNT test fail? Are there any
> situations where it would return erroneous results?
>
> I realize I'm being somewhat nitpicky on these matters but any feedback
> would be greatly appreciated!
>[/color]

[Louis Davidson]

Re: Trigger performance

> SELECT PrimaryKeyColum n FROM INSERTED[color=blue]
>
> opposed to:
>
> SELECT * FROM INSERTED[/color]

What for? You seldom want to return data from a trigger, so neither is
reall optimum. Can you explain what you are trying to do?
[color=blue]
> Secondly, what is the optimum way to determine which action fired an
> AFTER trigger that is defined for INSERT, UPDATE, DELETE? Is it
> something similiar to:[/color]

The optimum way to do this is to have three triggers, on for insert, update
and for delete. Then you don't have to spend resources determining which
kind of trigger it is, not to mention that the plan for the trigger will be
better if you don't have conditional execution of blocks:
[color=blue]
> IF NOT EXISTS(SELECT * FROM DELETED)
> --sql block for insert
> ELSE
> IF NOT EXISTS(SELECT * FROM INSERTED)
> --sql block for delete
> ELSE
> --sql block for update[/color]

If you must do this, this is adequate.
[color=blue]
> Lastly, is checking @@ROWCOUNT at the beginning of an AFTER trigger the
> best way to differentiate between multiple rows being affected or just
> a single row? Can using this @@ROWCOUNT test fail? Are there any
> situations where it would return erroneous results?[/color]

Yes, it is the best way, but it has to be the FIRST statement (other than
declararation of variables) So you need to do:

AS
declare @rowcount int
set @rowcount = @@rowcount

Then you can check @rowcount. You can just check @@rowcount instead of the
declare, but I generally set the value to a variable, since the number of
rows affected by the trigger can often be useful later in the trigger.
--
----------------------------------------------------------------------------
Louis Davidson - drsql@hotmail.c om
SQL Server MVP

Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -

http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)

"Scott CM" <scurriem@hotma il.com> wrote in message
news:1102719305 .071504.141400@ z14g2000cwz.goo glegroups.com.. .[color=blue]
>I have a multi-part question regarding trigger performance. First of
> all, is there performance gain from issuing the following within a
> trigger:
>
> SELECT PrimaryKeyColum n FROM INSERTED
>
> opposed to:
>
> SELECT * FROM INSERTED
>
> Secondly, what is the optimum way to determine which action fired an
> AFTER trigger that is defined for INSERT, UPDATE, DELETE? Is it
> something similiar to:
>
> IF NOT EXISTS(SELECT * FROM DELETED)
> --sql block for insert
> ELSE
> IF NOT EXISTS(SELECT * FROM INSERTED)
> --sql block for delete
> ELSE
> --sql block for update
>
> or is there a superior method?
>
> Lastly, is checking @@ROWCOUNT at the beginning of an AFTER trigger the
> best way to differentiate between multiple rows being affected or just
> a single row? Can using this @@ROWCOUNT test fail? Are there any
> situations where it would return erroneous results?
>
> I realize I'm being somewhat nitpicky on these matters but any feedback
> would be greatly appreciated!
>[/color]

[Erland Sommarskog]

Re: Trigger performance

Scott's posting didn't make it here (I found it on Google), so I'm adding
my replies to David's pos.

David Portas (REMOVE_BEFORE_ REPLYING_dporta s@acm.org) writes:[color=blue]
> 1.[color=green]
>> SELECT PrimaryKeyColum n FROM INSERTED
>>
>> SELECT * FROM INSERTED[/color]
>[/color]

Both of these seems wrong. Trigger should not normally return result sets.
Sometimes you want to do this for debugging, but leaving it around can
cause great confusion, because the client that executed the INSERT/UPDATE/
DElETE statment is not expecting a result set, and not consuming the
result set, can lead to the trigger not completing, the transaction not
being committed, and locks being held blocking other users.

All that said, I can't see any difference in effeciecy in this case,
since "inserted" is a virtual table, and there are no indexes.

And that leads to a question of trigger performance you didn't ask: if
your trigger makes several references to the inserted/deleted tables,
consider doing this first in your trigger:

INSERT @inserted (...)
SELECT ... FROM inserted

and then use the table variables in the following queries. The virtual
tables are slow, because they are read from the transaction log.

It might be tempting to do:

SELECT * FROM #inserted FROM inserted

since you don't have to declare a copy of the table, but since triggers
are always run in a transaction context, this can be costly, since the
creation of the temp table is logged. (Believe me, this is something I've
learnt the hard way.)
[color=blue]
> 2.[color=green]
>> Secondly, what is the optimum way to determine which action fired an
>> AFTER trigger that is defined for INSERT, UPDATE, DELETE?[/color]
>
> Try to keep trigger logic simple and set-based - you may be able to
> write a single DML statement rather than use multiple IFs in one
> trigger. If the logic is quite different in each case then create
> separate triggers for each action.[/color]

Scott's question is certainly meaningful. I often write a combined
INSERT/UPDATE trigger, because the same validations has to be performed.
For the same reason, I also include the DELETE action for the same trigger.
But some checks or cascading actions may only perform to one of them. In
that case it appears to be good for performance to not run that
check/action.

The way to check is:

IF EXISTS (SELECT * FROM @inserted) AND NOT (SELECT * FROM @deleted)
-- We have an INSERT statement.

[color=blue]
> 3.[color=green]
>> Lastly, is checking @@ROWCOUNT at the beginning of an AFTER trigger the
>> best way to differentiate between multiple rows being affected or just[/color][/color]

Probably. Although, I prefer to check SELECT COUNT(*) on inserted/deleted.
This may be due to that in earlier versions of SQL Server, @@rowcount did
not always have the right number.
[color=blue]
> Why would you care if one or multiple rows were updated? Triggers should
> use set-based code so the logic should be identical in any case. Never
> assume that only single rows will be updated - this is a classic error
> and one that causes a lot of grief judging by posts in this group.[/color]

There are indeed cases where you want to differentiate between a
singlerow/multirow operation:

o The primary key is updated, and you need to relate the old and new
value. Unless the table has an alternate key which is left unchanged,
you cannot handle multirow updates in this case. The one way out is
to disallow multi-row updates, by checking @@rowcount.

o The trigger logic for the general case for multi-row updates is much
slower than specific single-row logic, and you expect single-row
updates to be the most common. In this case you check @@rowcount,
and take different paths depending on the value. We have at least one
such trigger in our system.

o A variation of this is that the multi-row handling is complicated, and
there is only a requirement to handle single-row operation. After all,
most development budgets are limited. Of course, in this case too, you
need to check @@rowcount, and raise an error in case of a multi-row
operation.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Hugo Kornelis]

Re: Trigger performance

On Fri, 10 Dec 2004 15:25:38 -0800, oj wrote:
[color=blue]
>2. yes. you want to use if...then block for each type.
>
>e.g.
>declare @i int, @d int
>select @i=count(*) from inserted
>select @d=count(*) from deleted
>
>if @i=@d
>print 'update'
>if @i>@d
>print 'insert'
>if @i<@d
>print 'deleted'[/color]

Hi oj,

I'm sorry, but I have to disagree with this part of your message. Using IF
EXISTS or IF NOT EXISTS is quicker thatn actually counting the number of
rows in inserted and deleted, so if you only need to distinguish between
insert, update and delete (as per the original question), you should not
use COUNT(*).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[oj]

Re: Trigger performance

Yes, you probably gain some if this is a large set. Tks.

"Hugo Kornelis" <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message
news:kcomr0pu96 2614a67crku33s6 45oghq8rj@4ax.c om...[color=blue]
> On Fri, 10 Dec 2004 15:25:38 -0800, oj wrote:
>[color=green]
>>2. yes. you want to use if...then block for each type.
>>
>>e.g.
>>declare @i int, @d int
>>select @i=count(*) from inserted
>>select @d=count(*) from deleted
>>
>>if @i=@d
>>print 'update'
>>if @i>@d
>>print 'insert'
>>if @i<@d
>>print 'deleted'[/color]
>
> Hi oj,
>
> I'm sorry, but I have to disagree with this part of your message. Using IF
> EXISTS or IF NOT EXISTS is quicker thatn actually counting the number of
> rows in inserted and deleted, so if you only need to distinguish between
> insert, update and delete (as per the original question), you should not
> use COUNT(*).
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)[/color]