MS SQL Server - slow triggers with lots of rows?

Author

slow triggers with lots of rows?

sethpurcell@comcast.net

2006-02-03, 9:23 am

Hi there -

I'm experiencing slow inserts on tables with AFTER INSERT triggers on
them. I think it's because I'm joining to the [inserted] table to
determine which rows in the trigger table were just inserted, and there
are sometimes hundreds of thousands or even millions of rows inserted
by a single insert statement (moving from a staging table to the
trigger table). The join is an inner join on the single-column primary
key in the trigger table. What I'm wondering is: am I having trouble
because the [inserted] table isn't indexed? And if so, what can I do
about this? Do I have to chop up my inserts if I want to keep the
triggers fast? I would imagine the query optimizer would just do an
inner loop join with the inserted table on the outside and a PK seek
into the trigger table on the inside, but I can't find out if this is
what's going on because the inserted table doesn't exist outside of an
executing trigger's context. All ideas and suggestions are welcome.

Thanks!

Geoff N. Hiten

2006-02-03, 9:23 am

The problem is twofold. First, SQL 2000 does a log rewind to populate the
inserted and deleted tables. For a large number of rows, this can be a slow
undertaking. Second, once the tables are populated, they have no indexes.
SQL 2005 has a different mechanism to populate the inserted and deleted
tables, but it is still a bad idea to have triggers on tables with large
insert or update sets. I would move the trigger logic out to a stored
procedure where SQL can work on the real tables, not the virtual ones.

--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

<sethpurcell@comcast.net> wrote in message
news:1138974735.950671.91990@z14g2000cwz.googlegroups.com...
> Hi there -
>
> I'm experiencing slow inserts on tables with AFTER INSERT triggers on
> them. I think it's because I'm joining to the [inserted] table to
> determine which rows in the trigger table were just inserted, and there
> are sometimes hundreds of thousands or even millions of rows inserted
> by a single insert statement (moving from a staging table to the
> trigger table). The join is an inner join on the single-column primary
> key in the trigger table. What I'm wondering is: am I having trouble
> because the [inserted] table isn't indexed? And if so, what can I do
> about this? Do I have to chop up my inserts if I want to keep the
> triggers fast? I would imagine the query optimizer would just do an
> inner loop join with the inserted table on the outside and a PK seek
> into the trigger table on the inside, but I can't find out if this is
> what's going on because the inserted table doesn't exist outside of an
> executing trigger's context. All ideas and suggestions are welcome.
>
> Thanks!
>

sethpurcell@comcast.net

2006-02-03, 9:23 am

Thanks, Geoff. I neglected to mention the version of SQL Server I'm
using: 2000 upgrading to 2005 in a few months. Did you mean I should
drop the trigger or keep the trigger but avoid using the inserted
table? I guess this depends on what causes the inserted table to be
populated - just having a trigger on the table or having a trigger that
references the inserted table?

Also, can you recommend a SQL 2005 reference book that has this level
of detail (how inserted tables are populated, etc.)?

Thanks,
Seth

Geoff N. Hiten

2006-02-03, 11:23 am

I think the tables are populated during any trigger execution, regardless of
whether they are referenced but I am not certain. I suggest trying for
yourself.

Unfortunately, SQL 2005 books are a bit thin on the ground so far. I do
know Kalen Delaney is writing "Inside SQL Server 2005". Amazon shows the
first volume due out in March. Based on here books on SQL 7.0 and SQL 2000,
this will be a "must buy".

--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

<sethpurcell@comcast.net> wrote in message
news:1138977954.927635.84950@g44g2000cwa.googlegroups.com...
> Thanks, Geoff. I neglected to mention the version of SQL Server I'm
> using: 2000 upgrading to 2005 in a few months. Did you mean I should
> drop the trigger or keep the trigger but avoid using the inserted
> table? I guess this depends on what causes the inserted table to be
> populated - just having a trigger on the table or having a trigger that
> references the inserted table?
>
> Also, can you recommend a SQL 2005 reference book that has this level
> of detail (how inserted tables are populated, etc.)?
>
> Thanks,
> Seth
>

Kalen Delaney

2006-02-03, 11:23 am

As Geoff has said, the mechanisms are very different for SQL 2000 and SQL
2005.

For SQL 2000, there is no additional 'population' that has to be done for
the inserted and deleted tables. These tables are just views of the
transaction log which has already been populated with log records when the
insert, update or delete took place.

For SQL 2005 the inserted and deleted rows are stored in the version store
in tempdb to create the inserted and deleted tables. The version store gets
populated even if the tables are never accessed.

--
HTH
Kalen Delaney, SQL Server MVP
www. solidqualitylearning
.com

"Geoff N. Hiten" <SQLCraftsman@gmail.com> wrote in message
news:umE3cXNKGHA.3984@TK2MSFTNGP14.phx.gbl...
>I think the tables are populated during any trigger execution, regardless
>of whether they are referenced but I am not certain. I suggest trying for
>yourself.
>
> Unfortunately, SQL 2005 books are a bit thin on the ground so far. I do
> know Kalen Delaney is writing "Inside SQL Server 2005". Amazon shows the
> first volume due out in March. Based on here books on SQL 7.0 and SQL
> 2000, this will be a "must buy".
>
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
>
>
>
> <sethpurcell@comcast.net> wrote in message
> news:1138977954.927635.84950@g44g2000cwa.googlegroups.com...
>
>

Hugo Kornelis

2006-02-03, 8:23 pm

On 3 Feb 2006 05:52:16 -0800, sethpurcell@comcast.net wrote:

>Hi there -
>
>I'm experiencing slow inserts on tables with AFTER INSERT triggers on
>them. I think it's because I'm joining to the [inserted] table to
>determine which rows in the trigger table were just inserted, and there
>are sometimes hundreds of thousands or even millions of rows inserted
>by a single insert statement (moving from a staging table to the
>trigger table). The join is an inner join on the single-column primary
>key in the trigger table. What I'm wondering is: am I having trouble
>because the [inserted] table isn't indexed? And if so, what can I do
>about this? Do I have to chop up my inserts if I want to keep the
>triggers fast? I would imagine the query optimizer would just do an
>inner loop join with the inserted table on the outside and a PK seek
>into the trigger table on the inside, but I can't find out if this is
>what's going on because the inserted table doesn't exist outside of an
>executing trigger's context. All ideas and suggestions are welcome.

Hi Seth,

I have once had a similar problem. I had some triggers that had code
similar to this (using deleted in this example, but the same could
happen with inserted):

UPDATE b
SET Column = (expression on column from b and d)
, Other column = (other expression)
, .... repeat for a few more columns
FROM BaseTable AS b
INNER JOIN deleted AS d
ON d.PrimaryKeyColumn = b.PrimaryKeyColumn
WHERE b.SomeColumn = 'X'

After analyzing a profiler trace, I found that the optimizer picked a
VERY inefficient plan for this - instead of scanning deleted, then using
the clustered index to find a matching row in BaseTable, the plan would
scan BaseTable, then table-scan deleted for a match. Not efficient if
BaseTable has 6,000,000 rows and deleted has 200,000.

I fixed this by providing an optimizer hint:

UPDATE b
SET (same as above)
FROM deleted AS d
INNER LOOP JOIN BaseTable AS b
ON d.PrimaryKeyColumn = b.PrimaryKeyColumn
WHERE b.SomeColumn = 'X'
OPTION (FORCE ORDER)

Note that the tables have switched position and a hint ("LOOP") is added
between INNER and JOIN. The OPTION (FORCE ORDER) at the end is not
required (it is implied by the LOOP hint), but explicitly adding it
suppresses a warning message.

I have no idea if this will help you too - but you might give it a shot!

--
Hugo Kornelis, SQL Server MVP

sethpurcell@comcast.net

2006-02-06, 9:23 am

Thanks, Geoff for the help and great book recommendation.

Thanks, Kalen for the low-level scoop, and I look forward to getting
your book as soon as it comes out.

Thanks, Hugo for the query hint idea, I'll give this a try and see how
it performs. For now I've just moved the trigger logic into an SP that
runs after all the inserts have completed, but this leaves the DB in a
mildly inconsistent state that, while meeting the requirements of the
application, still bothers me, so I'd like to restore the triggers.

Sponsored Links