June 3, 2010 at 9:19 am
I have a scenario where i need to update a table (thru linked servers) based on the temporary table (#temp).
Table in Linked server has a Primary key column1 (Clustered Index)
Temporary table has Primary key column1 (Clustered Index)
Both tables have around 260814 rows.
I tried 2 approaches
Approach 1:
UPDATE <<Linked Server>>.tablename
SET
<<Column updates>>
FROM <<Linked Server>>.tablename link
JOIN #temp ON link.column1 = #temp.column1
Approach 2:
UPDATE link
SET
<<Column updates>>
FROM OPENQUERY(<<linkserver>>, ' select * from tablename ') AS link
JOIN #temp ON link.column1 = #temp.column1
Both of the above methods take long time (More than 2 hours).
Could you please suggest ideas/best practices by which i can improve above query.
Thanks
Suga
June 3, 2010 at 10:25 am
Whenever you need to execute a set based update which modifies data in the linked server table, the MSDTC will execute your updates row-by-row despite the fact that your statement is set-based. This makes it slow, but should not make it as slow as you describe. Could you please check whether you have any slowly executing triggers on update involved? If not then you can try the following approach:
1. Import data from your temp table to the linked server staging table (this should take no more than 10 seconds or so)
2. Modify your update statement to look as though you execute it while connected to the linked server locally (change the server_name.db_name.schema.table_name to just db_name.schema.table_name for your link table and change the references to the temp table to staging table which is now stored on the linked server.
3. Use the following statement to run your update:
declare @sql nvarchar(500);
set @sql = '
update link
set
<<column updates>>
from link_db_name.schema.tablename link
join your_staging_table temp on link.column1 = temp.column1;'
execute (@sql) at [Linked Server];
4. Dump the staging table
Please let me know if this works.
Oleg
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply