I have a sql script running on a server (ServerA)
This server, has a linked server set up (ServerB) - this is located off site in a datacenter.
This query works relatively speeidily:
SELECT OrderID
FROM [ServerB].[DBName].[dbo].[MyTable]
WHERE Transferred = 0
However, when updating the same table using this query:
UPDATE [ServerB].[DBName].[dbo].[MyTable]
SET Transferred = 1
It takes > 1 minute to complete (even if there's only 1 column where Transferred = 0)
Is there any reason this would be acting so slowly?
Should I have an index on MyTable for the "Transferred" column?
If you (I mean SQL server) cannot use index on remote side to select records, such remote update in fact reads all records (primary key and other needed fields) from remote side, updates these locally and sends updated records back. If your link is slow (say 10Mbit/s or less), then this scenario takes lot of time.
I've used stored procedure on remote side - this way you should only call that procedure remotely (with set of optional parameters). If your updateable subset is small, then proper indexes may help too - but stored procedure is usually faster.
UPDATE [ServerB].[DBName].[dbo].[MyTable]
SET Transferred = 1
WHERE Transferred = 0 -- missing this condition?
How often is this table being used?
If this table is used by many users at the same time, you may have a problem with lock/block.
Everytime some process updates a table without filtering the records, the entire table is locked by the transaction and the other processess that needs to update the table stand waiting.
It his case, you may be waiting for some other process to unlock the table.
