-
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
-
Post your query that joins with the remote server.
It may be doing full table scan on remote table.
-
Joins across linked servers are always performance killers. You should avoid it at all costs!!!
-
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
-
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
-
Forum Rules
|
|