Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Location
    Italy
    Posts
    7

    Unanswered: Transfer data between linked servers

    Hello to all,

    my problem is about the slow execution of a query.

    I have two database located in two different servers. The servers are linked with the linked server connection made with the enterprice manager.

    The databases structure is the same and I copy some data from a database to the other with the following query:

    INSERT INTO DATASERVER.RESULTS.dbo.Tab_Tests SELECT * FROM LINESERVER.WIP.dbo.Tab_Tests WHERE ID_Result = '{76271FC1-9470-4EF7-A403-000CF75C2215}' OR ID_Result = '{08C8EEB9-CD22-4CF9-8269-8C1CC58C752B}' OR ID_Result = '{B7B7EED1-36E4-48FA-9636-ABBE62AAE04D}' OR ID_Result = '{8F7BA69E-D2CE-4EB2-BBC4-AFFA165CE0CE}' OR ID_Result = '{273CB805-945F-4EFA-AE20-BC4E0898A19F}'

    Everything works till the database grows up more than 400 MB.

    Also if I select one result, that consist of some records, the query is very slow and the CPU is working over the 75 %.

    I ask you if my working way is corret or if you have some solution or some explanation for me.

    Thank you

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Check the query plan. It sounds like the table is being retrived to the local server to do the compare. This could be because the servers have different collations or the linked server doesn't have collation compatibility set on.

    It's always better to call an SP on the remote server so that you know where the code is being executed or failing that you can call sp_executesql on the reote server to extract the data.

Posting Permissions

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