Slow linked server update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Slow linked server update

Author

Topic

AndrejS
Starting Member

4 Posts

Posted - 2011-10-22 : 15:20:41

Hi, i have a problem, i cannot solve on my own, with updateing remote server's database using linked server. This is my original query, which is added together dynamically (which i cannot do with OpenQuery).

UPDATE Remote SET Remote.Atrib4 = TABLE_FROM.Atrib4, Remote.Atrib5 = TABLE_FROM.Atrib5, .....
from LINKED_SERVER.REMOTE_DB.dbo.TABLE_NAME Remote
join LOCAL_TABLE TABLE_FROM on TABLE_FROM.Atrib1 = Remote.Atrib1 AND TABLE_FROM.Atrib2 = Remote.Atrib2 AND TABLE_FROM.Atrib3 = Remote.Atrib3

At first, i thought, recomposing the query would help, but ....

UPDATE Remote SET Remote.Atrib4 = TABLE_FROM.Atrib4, Remote.Atrib5 = TABLE_FROM.Atrib5, .....
from LOCAL_TABLE TABLE_FROM, LINKED_SERVER.REMOTE_DB.dbo.TABLE_NAME Remote
where TABLE_FROM.Atrib1 = Remote.Atrib1 AND TABLE_FROM.Atrib2 = Remote.Atrib2 AND
TABLE_FROM.Atrib3 = Remote.Atrib3

didn't help. Although the TABLE_FROM only contains ONE !!! record, the query takes as much as 30 minutes to complete (ok, the remote table is some 500000+ record, which IS small, but my lines are obviously not the best and data needs to be transfered to my local server before doing any type of update, right?). Now, i can't transfer my LOCAL_TABLE table to remote server, because i cannot use "select * into linked_server.REMOTE_DB.dbo.TableRemote from TableLocal" and i don't have any other idea. But even if i can transfer data to remote server, there's the restiction of using dynamically composed query string in qpenquery syntax. Is there any hope i can peform this update in a reasonable time (say < 30 secs would be great) and if, how to do it? Oh, i'm using MSSQL 2k and 2k8 servers, so the sollution will also have to be 2k compatible.

Regards and thanks for any idea, that would bring me closer to 30 secs
Andrej

Kristen
Test

22859 Posts

Posted - 2011-10-22 : 17:22:02

"Although the TABLE_FROM only contains ONE !!! record"

Run it on the remote server, and query the TABLE_FROM into a #Temp table before the main query, and JOIN to #Temp

I expect that is possible using OpenQuery if you can't actually run it on the remote (e.g. because the Remote has no linked-server back to your Local server)

AndrejS
Starting Member

4 Posts

Posted - 2011-10-22 : 17:28:36

quote:

I expect that is possible using OpenQuery if you can't actually run it on the remote (e.g. because the Remote has no linked-server back to your Local server)

Unfortunately, i do not have linked servers defined on remote servers, so i cannot use "copy table to remote server" option.
"One record" was only used as an example to show the "speed" of execution, normally it ranges from 1 to 10000 records.

Kristen
Test

22859 Posts

Posted - 2011-10-22 : 17:46:22

I'll give some thought to whether I can think of a way to do that using OpenQuery from the Local server.

AndrejS
Starting Member

4 Posts

Posted - 2011-10-22 : 18:17:35

quote:
Originally posted by Kristen

I'll give some thought to whether I can think of a way to do that using OpenQuery from the Local server.

Well, this would help if the TABLE_FROM could be copied to the remote server, but i still have the issue with @Query variable in openquery call. I'll see if there's a way to do it. That way i would not have to wait for destination table to be transferred to my local server, right?

Kristen
Test

22859 Posts

Posted - 2011-10-22 : 18:41:35

"i still have the issue with @Query variable in openquery call"

You can fix that (I reckon) by creating the whole query dynamically.


SELECT @strQuery = 'SELECT * FROM REMOTE_DB.dbo.TABLE_NAME WHERE ...'

SELECT @strQuery = 'SELECT * FROM OpenQuery(LINKED_SERVER, '''
                 + REPLACE(@strQuery, '''', ''''')
                 + ''')'
EXEC (@strQuery)

but that, alone, doesn't help with your need to JOIN the local table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-23 : 02:18:25

quote:
Originally posted by Kristen

"i still have the issue with @Query variable in openquery call"

You can fix that (I reckon) by creating the whole query dynamically.

SELECT @strQuery = 'SELECT * FROM REMOTE_DB.dbo.TABLE_NAME WHERE ...'

SELECT @strQuery = 'SELECT * FROM OpenQuery(LINKED_SERVER, '''
+ REPLACE(@strQuery, '''', ''''')
+ ''')'
EXEC (@strQuery)

but that, alone, doesn't help with your need to JOIN the local table.

missed a quotes i guess


DECLARE @strQuery varchar(8000),@strQuery1 varchar(8000)

SELECT @strQuery1 = 'SELECT * FROM REMOTE_DB.dbo.TABLE_NAME WHERE ...'


SELECT @strQuery = 'SELECT * FROM OpenQuery(LINKED_SERVER, '''
                 + REPLACE(@strQuery1, '''', '''''')
                 + ''')'
EXEC (@strQuery)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AndrejS
Starting Member

4 Posts

Posted - 2011-10-23 : 04:40:06

quote:

but that, alone, doesn't help with your need to JOIN the local table.

Yes, that's true, but it will be the smaller TABLE_FROM, that will be transferred to remote server in order to update, while now, it's the larger remote TABLE_TO that's transferred.