Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2005
    Posts
    12

    Question Unanswered: Another linked server update problem

    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
    Last edited by AndrejS; 10-22-11 at 18:18.

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    The alias in your first query is mistyped.
    The remote server is a SQL Server???
    If so, will you always have date from a single record updating the 50,000-record table on the remote server?
    If always a single record, then I wouldn't bother using a table join, I'd just use the local table's data in the WHERE criteria, simplifying the query a touch.
    Not sure how indexes are involved with queries that span servers, but what is your index situtation on the remote table.?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Sep 2005
    Posts
    12
    Yeah, i mistyped it, thanks, will correct it.
    Remote is MSSQL, either 2k or 2k8, but with no linked servers defined, that's why i cannot copy (connect to remote and then "select into" from my local server) the local source table to remote server .
    One record was only to show how terribly slow the execution is, but in general it can range from 1 to 10000 records, this is a kind of replication, triggered in regular intervals.
    Since the sql is generated dynamically, i cannot rely on indexes, but that another story, too long to explain. But anyhow, indexes are set in a proper way to be used.
    As i see it, i would greatly improve the performance, if i could copy the TABLE_FROM to remote (linked) server and then use openquery (perhaps with exec(...) so i can use dynamic query @Query).
    Last edited by AndrejS; 10-22-11 at 18:23.

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    I am confused. You have two SQL Servers. You are trying to execute a query where a relatively small table is on your local server and a larger table is on the remote server. You have defined a linked server on the local server that is pointing to the remote server.
    Is what I have stated all correct?
    You mention something about the remote server not having any linked servers defined and that being the reason you can't copy from the local server to the remote server. I don't believe those two issues are related.
    Can you define a copy of the local table and create it on the remote server? You should be able to do this. Then, using the OPENQUERY syntax, you should be able to execute the entire query on the remote server and return records to the local server.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Sep 2005
    Posts
    12
    Quote Originally Posted by PracticalProgram View Post
    Is what I have stated all correct?
    Yes, alll of the above is correct.

    You mention something about the remote server not having any linked servers defined and that being the reason you can't copy from the local server to the remote server. I don't believe those two issues are related.
    Can you define a copy of the local table and create it on the remote server? You should be able to do this. Then, using the OPENQUERY syntax, you should be able to execute the entire query on the remote server and return records to the local server.
    Those two issues aren't related but with linked server on the other side, i could have connected to the remote and do the update from remote, which would in that situation perform all the work on remote side (copy the TABLE_FROM table and perform update based on it).
    Obviously, i'm missing the "copy table" part? How can i copy it to the remote server without using the "linked_server.remote_db.db_owner" syntax? I don't know how. And yes, i'll try to do it with openquery and hope for the best.

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    On the remote server create a duplicate table (structure only) of the LOCAL_TABLE.

    Then, from your local server, do the following:

    Code:
    DELETE
    FROM    LINKED_SERVER.REMOTE_DB.dbo.LOCAL_TABLE
    
    INSERT
    INTO    LINKED_SERVER.REMOTE_DB.dbo.LOCAL_TABLE
    SELECT  *
    FROM    dbo.LOCAL_TABLE
    
    UPDATE  Remote
    SET     Remote.Atrib4 = TABLE_FROM.Atrib4
            ,Remote.Atrib5 = TABLE_FROM.Atrib5
               , .....
    FROM    LINKED_SERVER.REMOTE_DB.dbo.TABLE_NAME Remote 
    INNER
    JOIN    LINKED_SERVER.REMOTE_DB.dbo.LOCAL_TABLE TABLE_FROM on
                TABLE_FROM.Atrib1 = Remote.Atrib1
                AND TABLE_FROM.Atrib2 = Remote.Atrib2
                AND TABLE_FROM.Atrib3 = Remote.Atrib3
    In other words, move all of the action over to the remote server.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  7. #7
    Join Date
    Sep 2005
    Posts
    12
    Yeah, i know that approach, but this is suitable for non dynamic tasks. Mine is dynamic (in time new attributes may be added to the structure), so i would have to compose the create statement on the fly and execute it on remote. Is there a way to only copy structure of the table without having to create the table first (sort of select into ... where 1=2)?

    Well, i tried the openquery approach and it improves performance when updateing my local table from remote

    UPDATE LOCAL_TABLE
    SET atrib2 = b.atrib2, atrib3 = b.atrib3
    FROM OPENQUERY(LINKED_SERVER, 'SELECT atrib1, atrib2, atrib3 FROM REMOTE_DB.dbo.REMOTE_TABLE
    where ....') b
    INNER JOIN LOCAL_TABLE A ON B.atrib1 = A.atrib1

    but i'm at the moment not getting anywhere with updateing remote with my local table. I guess with this script i'm missing the idea how to eliminate data transfer to local server.

    UPDATE LINKED_SERVER.REMOTE_DB.dbo.REMOTE_TABLE
    SET atrib2 = a.atrib2, atrib3 = a.atrib3
    FROM OPENQUERY(LINKED_SERVER, 'SELECT atrib1, atrib2, atrib3 FROM REMOTE_DB.dbo.REMOTE_TABLE') b
    INNER JOIN LOCAL_TABLE a ON b.atrib1 = a.atrib1

    This takes an awful lot of time to execute, there's practically no gain. Is it possible to use openquery when trying to update remote table with data from local table?

  8. #8
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Because of the dynamic nature of you table structure, you are going to have to dynamically create the table (using sysobjects and syscolumns, or their more recent equivalents) via an EXEC statement.

    Using the methodology I outlined in my posting of 9:58 yesterday, the DELETE query can be executed directly, but the INSERT and UPDATE queries will have to be dynamically generated and executed using the EXEC.

    If you do it correctly, and the indexes are correct, I see no reason why this whole operation should not execute instantaneously, even though it is dynamic SQL.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  9. #9
    Join Date
    Sep 2005
    Posts
    12
    Quote Originally Posted by PracticalProgram View Post
    If you do it correctly, and the indexes are correct, I see no reason why this whole operation should not execute instantaneously, even though it is dynamic SQL.
    I've tried the openquery approach and mostly it really boosts execution, but i seem lost with my problem. I'm first trying a simple example, when i solve this first step, i'll introduce the dynamic sql query.

    I tried the following script

    UPDATE b
    SET atr2 = a.atr2
    FROM OPENQUERY(LS, 'SELECT atr1, atr2 FROM rem_db.dbo.remote_table') b
    INNER JOIN local_table a ON b.atr1 = a.atr1

    or

    UPDATE b
    SET b.atr2 = a.atr2
    FROM OPENQUERY(LS, 'SELECT atr1, atr2 FROM rem_db.dbo.remote_table') b
    INNER JOIN local_table a ON b.atr1 = a.atr1

    and it returns error message.

    Server: Msg 208, Level 16, State 1, Line 1
    Invalid object name 'b'.

    What am i doing wrong (apart from using alias where not allowed )

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •