Link Server Optimization Help

Question

I have this code in a trigger.

if isnull(@d_email,'') <> isnull(@i_email,'')
 begin
 update server2.database2.dbo.Table2
 set
 email = @i_email,
 where user_id = (select user_id from server2.database2.dbo.Table1 where login =  @login)
 end

I would like to update a table on another db server, both are MSSQL. the query above works for me but it is taking over 10 seconds to complete. table2 has over 200k records. When I run the execution plan it says that the remote scan has a 99% cost.

Any help would be appreciated.

Answer 1

Whoa, bad trigger! Never and I mean never, never write a trigger assuming only one record will be inserted/updated or deleted. You SHOULD NOT use variables this way in a trigger. Triggers operate on batches of data, if you assume one record, you will create integrity problems with your database.

What you need to do is join to the inserted table rather than using a varaible for the value.

Also really updating to a remote server may not be such a dandy idea in a trigger. If the remote server goes down then you can't insert anything to the orginal table. If the data can be somewaht less than real time, the normal technique is to have the trigger go to a table on the same server and then a job pick up the new info every 5-10 minutes. That way if the remote server is down, the records can still be inserted and they are stored until the job can pick them up and send them to the remote server.

Answer 2

First, the obvious. Check the indexes on the linked server. If I saw this problem without the linked server issue, that would be the first thing I would check.

Suggestion: Instead of embedding the UPDATE in the server 1 trigger, create a stored procedure on the linked server and update the records by calling the stored procedure.

Try to remove the sub-query from the UPDATE:

if isnull(@d_email,'') <> isnull(@i_email,'')
begin
    update server2.database2.dbo.Table2
       set email = @i_email
      from server2.database2.dbo.Table2 t2
           inner join
           server2.database2.dbo.Table1 t1
           on (t1.user_id = t2.user_id)
     where t1.login = @login
end