Post reply

Cross Database Joins with Different SQL Servers

  • May 15, 2007 at 9:41 pm

    #103362

    We have a couple of Databases that exist on different servers thus preventing us from doing Joins in our SQL calls. Does anyone have any suggestions on how we can efficiently do a join using multiple SQL Servers in .net without replication or linked servers?

  • May 15, 2007 at 11:01 pm

    #706656

    you can add linked servers (sp_addlinkedserver) and use this select method :

     

    Select *  linkedServerName.dbName.dbo.tblName  t1 inner join l1.db2.dbo.t2 t2 on ...

  • May 17, 2007 at 9:01 am

    #707177

    If you do not want to create a linked server You can write A ACtive X script with Dynamic properties where you going to include all you connection variabes

    Kindest Regards,

    Web programmer

  • May 17, 2007 at 11:48 pm

    #707309

    "Does anyone have any suggestions on how we can efficiently do a join using multiple SQL Servers in .net without replication or linked servers?"

    the terms "efficient" and "join using multiple SQL Servers" do not go together.  cross server joins are not efficient.

    ---------------------------------------
    elsasoft.org

  • May 18, 2007 at 6:47 am

    #707408

    I can only think of 1 method . You need to create 2 connections 1 to your source and another to you destination. Try to dump data in temp tables in this case you would not have to deal with cross server connection and then do the joins on your destination and temp tables

    Kindest Regards,

    Web programmer

  • May 18, 2007 at 3:20 pm

    #707594

    the terms "efficient" and "join using multiple SQL Servers" do not go together.  cross server joins are not efficient.

     
    That's the right answer.  This type of design will eventually lead to one extremely slow application and if you don't do a very good job of educating users, developers, and managers, someone will come looking for you to explain why all their employees are angry.

  • May 21, 2007 at 12:09 pm

    #707891

    You're facing two problems:

    1. joining across two servers is inefficient (this has already been pointed out)
    2. replicating the entire table just before performing the join is also inefficient and may significantly impact the query -- not to mention your network

    So, the best alternative may be to maintain a copy of the remote table on the local server (the server on which you perform the query). However, there is a good chance that you don't need all the columns of the remote table. So your local copy need only contain the columns you actually use in the query. I don't know your situation, but this could make a tremendous difference. Then all you have to do is decide how often your local table is updated: at some specified interval or whenever the remote table is modified.

    Tomm Carr
    --
    Version Normal Form -- http://groups.google.com/group/vrdbms

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply