SQL Server Performance Forum – Threads Archive

linked servers and triggers

We are trying to insert records into a table from a remote server using "linked server" on the source server. The table on the target server is audited using a trigger (for each insert update and delete, a record is added in an audit table). Our problem is that the insert from the linked server is really slow (48 hours to do less than 500,000 records) Does anyone know if there are any issues with inserts on tables with triggers from linked servers? Olle, London

Did you try disabling the trigger on the table and running the job again? How are you inserting the records? I mean is it a batch operation or a record by record basis? If it is a record by record basis, I would recommned copying the data to the destination database and then running the job locally. Saves network round trips. If you are running in a batch, (I am not very sure of this.. Somebody plz confirm) the trigger is fired only for the last record and not all the records inserted in the table. Although it seems like a bug, but it may be a bliss in your case. Gaurav

Thanks My solution in the end was to set up a dts that first copies the entire table from source into a temporary table on the target database. I then run the insert locally. This seems to speed up the process a lot (although i’m still waiting for it to finish). Thanks anyway. /Olle

quote:Originally posted by gaurav_bindlish Did you try disabling the trigger on the table and running the job again? How are you inserting the records? I mean is it a batch operation or a record by record basis? If it is a record by record basis, I would recommned copying the data to the destination database and then running the job locally. Saves network round trips. If you are running in a batch, (I am not very sure of this.. Somebody plz confirm) the trigger is fired only for the last record and not all the records inserted in the table. Although it seems like a bug, but it may be a bliss in your case. Gaurav

Olle, London

Something that could speed up inserts is changing the recovery model on the destinatin server to bulk. The cost would be a bigger transaction log backup after. Bambola.

Removing indexes from the target table before the inserts may be of great help also. You could always re-create the indexes after. Nathan H.O.

Sorry, there is a correction to the previous post. Triggers are executed for all the rows affected and not just for the last row. I saw this behaviour in a normal update. However I had heard that triggers do not exeute for all statements in case of bulk copy / Dts etc. Has anyone experienced this before? Gaurav

Triggers can be fired from bulk operations using the hint FIRE_TRIGGERS. Which rows they will affect depends on how they are written. Sometimes programmers that haven’t got much experience writing triggers do not write them to affect more than one row. It’s not a problem of triggers in general, but of programming.
As for removing indexes, it can be a good idea when the number of the rows inserted is bigger than 30% (if I am not mistaken) of the rows in table. Bambola.

quote:Originally posted by bodelius ……. The table on the target server is audited using a trigger (for each insert update and delete, a record is added in an audit table)……..

The triggers in this scenario must be fired. Even if a positive shift in the overall performance would be realized by disabling their firing, this would violate the business rules. The "30% rule" will also be affected by the index fill factor, type and number of indexes in the table etc. Referential integrity (Foreign Keys) and constraints (Unique, CHECK) will also affect the performance of any Insert statement. Nathan H.O.

]]>