Mike,

Thanks for the update. The reason we've used temp tables is that the code is much easier to read and understand. However, you're right that this is much slower. I run some tests. Here are the results:

Update 10,000 rows

*-- Test 1: No Trigger
8 seconds

*-- Test 2: Trigger using temp tables
86 seconds!

*-- Test 3: Same trigger, but with a single SELECT
15 seconds

I was quite surprised to see such a differnce. The use of temp tables really seems to slow things down. I've also noticed that there's a lot of disc activity with Test 2 compared to Test 3.

Thanks again for the tip!

>Since you're not really using #temptable maybe the two can be combined using a derived table. Also, there's better performance if you create the temp. table and then fill it, instead of using the SELECT INTO configuration.
>
>SELECT
> CustomerId
> INTO #TempTable2
>FROM (
> SELECT del.CustomerID,del.CompanyName,del.ContactName,del.ContactTitle,
> del.Address,del.City,del.Region,del.PostalCode,del.Country,del.Phone,del.Fax
> FROM deleted del
>UNION
> SELECT ins.CustomerID,ins.CompanyName,ins.ContactName,ins.ContactTitle,
> ins.Address,ins.City,ins.Region,ins.PostalCode,ins.Country,ins.Phone,ins.Fax
> FROM inserted ins ) AS x
>GROUP BY
> customerId
>HAVING
> COUNT(customerId) > 1
>
>I also think you should look at look at removing the temp table all together
>by using the above as a subquery of the other two INSERT statements:
>
>INSERT INTO CUSTOMERS_AUDIT (CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax,cTrnsTyp)
>SELECT del.CustomerID,del.CompanyName,del.ContactName,del.ContactTitle,del.Address,del.City,del.Region,del.PostalCode,del.Country,del.Phone,del.Fax,'U' FROM deleted del
>WHERE CustomerId IN (
>
>SELECT
> CustomerId
> INTO #TempTable2
>FROM (
> SELECT del.CustomerID,del.CompanyName,del.ContactName,del.ContactTitle,
> del.Address,del.City,del.Region,del.PostalCode,del.Country,del.Phone,del.Fax
> FROM deleted del
>UNION
> SELECT ins.CustomerID,ins.CompanyName,ins.ContactName,ins.ContactTitle,
> ins.Address,ins.City,ins.Region,ins.PostalCode,ins.Country,ins.Phone,ins.Fax
> FROM inserted ins ) AS x
>GROUP BY
> customerId
>HAVING
> COUNT(customerId) > 1
>
>)
>
>
>-Mike