December 19, 2008 at 3:48 pm
It seems that Update statement executing over Linked server has lower priority to compare with local one... I try to update 1 record only on Linked server and it takes forever (same story while cancelling this query). Select works instantaneously, i.e. probably it’s not connection issue. Same Update issued on Local Sql server works like a charm. Just curious- what can be the reason for so big execution time difference? Thanks
December 19, 2008 at 9:38 pm
probably u may want to go through these links... similar issues..
http://bytes.com/groups/ms-sql/79584-remote-database-updates-take-long-time-complete
December 19, 2008 at 9:51 pm
Any updates over a linked server requires a distributed transaction, using MSDTC. There is quite a bit of overhead involved using a distributed transaction, and that is what contributes to the extra time required.
December 20, 2008 at 9:14 am
Thanks gents for your help.
Pradeep: Unfortunately these 2 links you provided me with is not my case- I have only 1 record to update and no function involved in Update.
Lynn:distributed transaction overhead you mentioned should participate in "SELECT" statement also but execution time for "select" ( 5min)
is so diffirent (and Update cancelling lasts forever).
I googled this issue and found a lot of similar questions. Every time (only in few cases) solution was found it was not general one but rather individual issue.
December 20, 2008 at 10:52 am
A select statement won't start a distributed transaction unless you are explicitly starting one.
We had that issue with linked server queries as well. You need to search this site as i am sure there are additional threads that talk about this very issue. You need to verify how MSDTC is configured on both systems.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply