Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

How to join a local table to a remote table by way of a linked server Expand / Collapse
Author
Message
Posted Thursday, March 1, 2012 8:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 21, 2015 1:25 PM
Points: 296, Visits: 642
Working in an environment with multiple security bands, multiple networks and load balanced servers there's no way linked servers are possible, practical, or desired.

SSIS or some other tool that allows heterogeneous data connects is a good solution to dealing with these situations.

In a smaller shop linked servers might be the preferred solution but I really haven't seen it done in any of my work environments since we brought in 2005.
Post #1260192
Posted Thursday, July 23, 2015 3:52 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:57 PM
Points: 334, Visits: 357
My company has central database server and all other database servers subscribe to that central server to keep a local copy (nightly replicated). That way I dont need to use linked server queries to read variables from central db server. We are using 3 part queries (db.schema.tablename) on same server. Now managers want to have the central server keep server details, that means make all 3 part queries to become linked server queries.
I am opposing that but dont have alternatives. Pulling to local table does not work as there are too many tables. In short, managers want to execute everything from central db server by using serverIDs for other servers. Any suggestions to address this?


Thanks,
Naveen.
Every thought is a cause and every condition an effect
Post #1705364
Posted Friday, July 24, 2015 6:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:36 PM
Points: 2,606, Visits: 6,457
Naveen PK (7/23/2015)
My company has central database server and all other database servers subscribe to that central server to keep a local copy (nightly replicated). That way I dont need to use linked server queries to read variables from central db server. We are using 3 part queries (db.schema.tablename) on same server. Now managers want to have the central server keep server details, that means make all 3 part queries to become linked server queries.
I am opposing that but dont have alternatives. Pulling to local table does not work as there are too many tables. In short, managers want to execute everything from central db server by using serverIDs for other servers. Any suggestions to address this?


Any query, that involves context switching between multiple database instances and pulling data across the network wire, will perform significantly slower than a query that joins only to tables within it's own instance. Even with optimal indexing and expert usage of query hints, you will still be looking at a 25% runtime increase minimal. The network wire is a bottleneck, and there is no way to get around that, unless you co-locate all the instances on the same physical machine.

The best way to approach this would be to take one of your more complex stored procedures, retrofit it to use distributed joins, deploy it to production under a different name, and then compare the performance of the distributed join version versus the locally joined version using same parameters. If you can prove that distributed queries will significantly impact your queries in a bad way, then you will create F.U.D. in the mind of the whomever is advocating this idea and hopefully they'll forget the whole thing.
https://en.wikipedia.org/wiki/Fear,_uncertainty_and_doubt

Or... maybe after experimenting with it you'll discover it's not a significant impact after all.

However, if management is hell bent on going down this path of distributed joins regardless of the impact, then there are ways to mitigate the issue. For example, you can use OPENQUERY to pull the remote recordset you need into a local temp table, and then join your local tables to the temp table.

http://blogs.msdn.com/b/sqlsakthi/archive/2011/05/09/best-performer-distributed-query-four-part-or-openquery-when-executing-linked-server-queries-in-sql-server.aspx



You are standing in an open field west of a white house, with a boarded front door.... Opening the small mailbox reveals a leaflet.

> read leaflet

"ZORK is a game of adventure, danger, and low cunning. In it you will explore some of the most amazing territory ever seen by mortals." http://www.web-adventures.org/cgi-bin/webfrotz?s=Zork1
Post #1705516
Posted Friday, July 24, 2015 12:54 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:57 PM
Points: 334, Visits: 357
Yes Eric, it is worth experimenting to find the performance issues before saying yes or no. I would triple (0r based on growth rate) the data in some large tables to ensure that this works when data grows in future.

Thanks,
Naveen.
Every thought is a cause and every condition an effect
Post #1705683
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse