dcsimg
Results 1 to 5 of 5

Thread: Performance problems updating a remote database using triggers

  1. #1
    Join Date
    Apr 2005
    Posts
    3

    Performance problems updating a remote database using triggers

    I have a table that has triggers on insert update and delete. The triggers perform modifications on another table on a remote databased(linked server).
    All triggers make a join between the tables inserted, deleted and the remote table.
    When running a profile on the remote database it seems that the following query is done on the remote table by the triggers.

    "Select * from Database.dbo.Table"

    The triggers are the only entities accessing the remote table from the original server so the query must come from them. My only conclusion is that MSSQL server is doing this query for some kind of "optimization".

    Has anyone seen this before?
    How can I work arond this? The remote table is BIG and this query happening every few seconds in a problem for me.

    Thanks

    Robert

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,918
    Post your query that joins with the remote server.

    It may be doing full table scan on remote table.

  3. #3
    Join Date
    Feb 2003
    Posts
    1,048
    Joins across linked servers are always performance killers. You should avoid it at all costs!!!

  4. #4
    Join Date
    Apr 2005
    Posts
    3
    Thanks for the responses.
    After checking some execution plans it looks like SQL server is bringing a local copy of the linked table and works with it.
    I am removing all joins between linked servers in triggers to fix the problem at the moment.

    Robert

  5. #5
    Join Date
    Feb 2003
    Posts
    1,048
    I do a lot of cross-server data processing, and it is actually faster to bring over a whole table into a local temp table and then process the data in it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •