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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-10-11 : 18:40:14
|
| PUTANE writes "HOW TO JOIN TABALE WHICK IS LOCATED IN 2 DIFFERENT SERVER" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-10-11 : 18:41:47
|
| Don't. The performance will be horrible. Pull the data that you need from Server A over to Server B and process it all on Server B. Cross-server joins incur a significant amount of overhead that can slow queries down by 20-50 times, or more. |
 |
|
|
heze
Posting Yak Master
192 Posts |
Posted - 2006-10-11 : 19:28:57
|
| hi I query 2 servers all the time and join their tables, although I completely agree with you Rob about the performance, when you do a lot of queries (using tables in both locations), the time lost importing the data becomes overwhelming, furthermore, keeping track of which tables need to be updated becomes a nightmare and make evrybody depending on the result of your query wonder if you queried the updated table, of course, this is jusyt in my case where I query both tables over and over again and of course, given the fact that I am not the boss to put everything in one single server |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-10-11 : 19:42:14
|
| Yeah, I should qualify that statement a bit..."don't do cross server joins if you have more than a few hundred rows on either side." Otherwise you end up with cross-join behavior that can generate a million rows (or the equivalent overhead).One technique I use is remote stored procedure execution. I set up a procedure on Server B that accepts parameters for the data I want to retrieve. I call that procedure from Server A using 4-part names or openquery, and insert its results into a temp table on Server A. I do this as many times as needed to process all the data I need to join. This provides the benefit of fresh data and avoids the overhead of a cross server join and distributed transactions. It's usually faster too because the execution plans are cached and processed on one server only. |
|
|
|
heze
Posting Yak Master
192 Posts |
Posted - 2006-10-11 : 23:24:17
|
| Rob, what is the advantage of doing the procedure rather than just do aselect {myFields}into #myTempfrom remoteServer.remDatabase..myRemTablewhere {someconditions}do whatever with #myTemp? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-10-11 : 23:37:32
|
| The stored procedure can cache its execution plan on the remote server, for one thing. Doing the following:INSERT #myTemp SELECT * FROM openquery(remoteserver, 'EXEC myDB.dbo.myProc')lets the procedure execute remotely, lowering (possibly eliminating, I haven't tested) the need for a distributed transaction. There's other overhead that it would avoid as well (think linked ODBC tables in Access). I've been using this technique for a while after noticing a significant improvement over the form you posted above. I'm not 100% sure than OPENQUERY gives the best performance either, but the way I read BOL suggested that it would not enlist a distributed transaction whereas a 4 part name (EXEC remoteserver.myDB.dbo.myProc) would. It's probably worth doing some tests using all the possible forms to see which one gives you the best performance. |
|
|
|
|
|
|
|
|
USE OF JOIN