Prev: select statement - multiple values returned
Next: query on max() - Moby
From: Ian Boyd on 4 Jul 2008 11:36
Greetings,

We're pulling values from a the "inserted" table that is created in an
insert trigger. When we do an insert and view the execution plan in
Query Analyzer, the "Inserted Scan" accounts for 98% of the entire
query cost. How can this be optimized? There is very little
documention on this operation. Any help would be greatly appreciated.

The trigger (trimmed down to the bare essentials to reproduce the problem):


CREATE TRIGGER LogInsert_EventGuests ON [dbo].[EventGuests]
FOR INSERT
AS

INSERT INTO AuditLog(
ChangeDate, RowGUID, ChangeType,
Username, HostName, AppName,
TagGUID, Tag)
SELECT
getdate(), --ChangeDate
i.EventGuestGUID, --RowGUID
'INSERTED', --ChangeType
USER_NAME(), HOST_NAME(), APP_NAME(),
i.EventGUID, --TagGUID
i.PatronName --Tag
FROM Inserted i


Description in Query Analyzer execution plan:
Inserted Scan
Scanning the pseudo-table "inserted" within a trigger.
Physical Operation: Inserted Scan
Logical Operation: Logical Scan
Row count: 0
Estimated row size: 223
I/O cost: 13.3
CPU cost: 0.000080
Number of executes: 1
Cost: 13.333214(100%)
Subtree cost: 13.3
Estimated row count: 1


Interestingly, even though no rows are being inserted, the insert trigger
still runs, and dominates the query.
INSERT INTO EventGuests (...)
SELECT ...


From: Russell Fields on 4 Jul 2008 12:53
Ian,

SQL Server 2000 and 2005 implement the inserted and deleted tables
differently, but they are both managed by SQL Server. In other words, you
have no real control over the scan of them.

I would not have expected an INSERT trigger to fire if there were no inserts
(but maybe I am missing something) but you could start the trigger with:

if @@ROWCOUNT = 0
return

RLF



"Ian Boyd" <ian.msnews010(a)avatopia.com> wrote in message
news:eCMwvue3IHA.1240(a)TK2MSFTNGP05.phx.gbl...
> Greetings,
>
> We're pulling values from a the "inserted" table that is created in an
> insert trigger. When we do an insert and view the execution plan in
> Query Analyzer, the "Inserted Scan" accounts for 98% of the entire
> query cost. How can this be optimized? There is very little
> documention on this operation. Any help would be greatly appreciated.
>
> The trigger (trimmed down to the bare essentials to reproduce the
> problem):
>
>
> CREATE TRIGGER LogInsert_EventGuests ON [dbo].[EventGuests]
> FOR INSERT
> AS
>
> INSERT INTO AuditLog(
> ChangeDate, RowGUID, ChangeType,
> Username, HostName, AppName,
> TagGUID, Tag)
> SELECT
> getdate(), --ChangeDate
> i.EventGuestGUID, --RowGUID
> 'INSERTED', --ChangeType
> USER_NAME(), HOST_NAME(), APP_NAME(),
> i.EventGUID, --TagGUID
> i.PatronName --Tag
> FROM Inserted i
>
>
> Description in Query Analyzer execution plan:
> Inserted Scan
> Scanning the pseudo-table "inserted" within a trigger.
> Physical Operation: Inserted Scan
> Logical Operation: Logical Scan
> Row count: 0
> Estimated row size: 223
> I/O cost: 13.3
> CPU cost: 0.000080
> Number of executes: 1
> Cost: 13.333214(100%)
> Subtree cost: 13.3
> Estimated row count: 1
>
>
> Interestingly, even though no rows are being inserted, the insert trigger
> still runs, and dominates the query.
> INSERT INTO EventGuests (...)
> SELECT ...
>


From: Ian Boyd on 4 Jul 2008 13:18
> I would not have expected an INSERT trigger to fire if there were no
> inserts (but maybe I am missing something) but you could start the trigger
> with:

That was just an artifact of this particular test query i'm running this
time. Normally rows actually will be added.

i'm hoping to understand what the inserted pseudo-table actually is - behind
the scenes.

Is it doing a table scan of the underlying table, and only picking out the
rows that it just modified? If that's true then there's nothing i can do -
and the underlying table has 300k rows. But maybe if i have some index or
cluster, then the algorithm that SQL Server uses to build the inserted
virtual table can do a clustered index seek or index seek with a bookmark
lookup.



From: Russell Fields on 4 Jul 2008 13:30
Ian,

For SQL Server 2000, the inserted and deleted pseudo tables are produced by
scanning the transaction log.

So, if you use inserted or deleted more than once in a trigger, you can be
helped by putting it into tempdb and perhaps indexing it as well.
(Although, I would in general hope that you are not doing so much in a
trigger that creating an index on the fly would be important for
performance.)

SELECT * INTO #inserted FROM inserted

For SQL Server 2005, the inserted and deleted tables are created by the
row-versioning feature, which uses tempdb to hold the rows. This is
explained in: http://msdn.microsoft.com/en-us/library/ms189050.aspx

Again, multiple uses of inserted or deleted may benefit by being put into a
temporary table.

Of course, if you use inserted only once to insert to an audit table, then
the temporary table is just more overhead than help.

FWIW,
RLF

"Ian Boyd" <ian.msnews010(a)avatopia.com> wrote in message
news:ehFp3nf3IHA.3480(a)TK2MSFTNGP03.phx.gbl...
>> I would not have expected an INSERT trigger to fire if there were no
>> inserts (but maybe I am missing something) but you could start the
>> trigger with:
>
> That was just an artifact of this particular test query i'm running this
> time. Normally rows actually will be added.
>
> i'm hoping to understand what the inserted pseudo-table actually is -
> behind the scenes.
>
> Is it doing a table scan of the underlying table, and only picking out the
> rows that it just modified? If that's true then there's nothing i can do -
> and the underlying table has 300k rows. But maybe if i have some index or
> cluster, then the algorithm that SQL Server uses to build the inserted
> virtual table can do a clustered index seek or index seek with a bookmark
> lookup.
>
>
>


From: Ian Boyd on 4 Jul 2008 13:57
> For SQL Server 2000, the inserted and deleted pseudo tables are produced
> by scanning the transaction log.
>
> So, if you use inserted or deleted more than once in a trigger, you can be
> helped by putting it into tempdb and perhaps indexing it as well.
> (Although, I would in general hope that you are not doing so much in a
> trigger that creating an index on the fly would be important for
> performance.)
>
> SELECT * INTO #inserted FROM inserted

Interesting idea. i was under the impression that the inserted and deleted
tables were "in-memory" tables. i was under that impression because it says
that in the BOL :)

Although this particular trigger is pretty much as you see it - just the one
insert, i do have plenty of other triggers that reference the inserted and
deleted pseudo-tables n-times (n being the number of column i want to audit
for an audit log). i should look into any potential performance gains for
those cases.


> For SQL Server 2005, the inserted and deleted tables are created by the
> row-versioning feature, which uses tempdb to hold the rows. This is
> explained in: http://msdn.microsoft.com/en-us/library/ms189050.aspx

Interesting technology. i would not, and am still not quite sure, what value
Row Versioning is to the client - but if it makes triggers better than what
do i care. :P


> Again, multiple uses of inserted or deleted may benefit by being put into
> a temporary table.
>
> Of course, if you use inserted only once to insert to an audit table, then
> the temporary table is just more overhead than help.

Yeah, no. Booo.


 |  Next  |  Last
Pages: 1 2
Prev: select statement - multiple values returned
Next: query on max() - Moby