Trigger Performance Problem / Question

[crapsolo]

Using SQL Server 2000 8.0 - SP4

Hi,
I've got pretty nasty trigger that is starting to cause some performance issues. Without getting into why there should or shouldn't have a trigger used in this situation, I had a question about a idea I had as a temporary fix.

Basically this trigger is on INSERT only, and is inserting into three other tables. Two of three queries are big and nasty with nested max queries, etc.

The table this trigger is on is used much more frequently in our application than the three history tables it is inserting into. My question is, will I gain anything or take some of the hit off of this table if I take two of the three queries into a NEW trigger on one of the history tables instead?

So essentially I'm proposing going from this:
IMPORTANT_TABLE Trigger =
(small insert-historytable1, big insert-historytable2, big insert-historytable3)

to this:
IMPORTANT_TABLE Trigger = (small insert-historytable1 )
+
historytable1 Trigger = (big insert-table2, big insert-table3)


Thanks in advance and sorry if that was a totally bizarre way to explain/illustrate. Basically I'm wondering if moving the work of the trigger to another trigger on another table will help.

[chazzy]

just wondering, because i hate the idea of history stuff, are you using nolock's?

[crapsolo]

No, I'm not.

[chazzy]

would it be feasible for you to use them? or is the data highly volatile?

[crapsolo]

I think it would be feasible and is a great suggestion. Thank you.

But I would still like to know the answer to the question of moving the "workload" so to speak. Maybe no one can answer though and I will just try it. But I will need to look at all aspects to improve this overall, so I am open to other suggestions.

[chazzy]

i'm not 100% certain, but I believe that even moving 2 of the 3 queries to a history table will still slow you down, since you need to complete the sql statement.

[mattyblah]

have you tried to clean up the queries in the triggers? from experience i've rewritten queries that take minutes to take seconds.