January 16, 2007 at 9:14 am
Anyone had performance problems when updating a remote SQL db? Its strange I can SELECT thousands of rows in around 5 seconds but it takes 2 minutes to update one date field in 50 rows! Heres what I'm trying;
UPDATE LnkS
SET LnkS.foca = LT.FOCdateRtnd
FROM Local_tbl AS LT
INNER JOIN linkedserver.database.table AS LnkS
ON LnkS.orderid = LT.orderid
Maybe UPDATE OPENQUERY would work better but I haven't figured out how to use it yet...
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer
January 16, 2007 at 11:18 am
It is better if you only update local tables. You can rewrite your query to run on the remote server.
It's probably not the best solution, but you can do this:
exec linkedserver.database.dbo.sp_executeSQL N'your update statement writtento be run ont he remote server)'
You can also encapsulate the upate in a stored proc and call it the same way exec linkedserver.database.dbo.yourproc @orderid
It's a hack, but it will work. I don't do much with distributed queries, I'm sure someone here could tell you the 'correct' approach.
SQL guy and Houston Magician
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply