| Author |
Topic |
|
PagZillA
Starting Member
4 Posts |
 Posted - 2010-02-01 : 14:43:37
|
| Howdy folks, Spent the last hour or so browsing the forums (amongst many others) and couldn't find anything to direct me...I have a list of about 200,000 unit ID’s in a local database, which I need to pair up some information from across a linked server. (the table I am gathering information from has millions of records).I’m aware of the horrendous join speeds across a linked server, so I’m trying to get the data I need to join into a local table prior to doing any joining. A solution I thought I’d try is take the MIN & MAX value of the unit ID range and restrict the query results to that range, and pull all of that data into a local table for all of my joining needs, however, the difference between the two restricting values is 36,089,927,805 and there could be any amount of records with that matching criteria.. a join would be much better.MAX U_ID: 344728814042MIN U_ID: 308638886237I have read-only access to the linked server, and this list of 200,000+ unit id’s will change week to week, so I can't have a list applied to that database/server.Any thoughts/suggestions on how I could accomplish this task without having a query pull hundreds of thousands (or more) of unneeded records and to avoid the horrendous run-time caused by the cross-server’s join?Thanks!! Never let formal education get in the way of your learning - Mark Twain |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2010-02-01 : 16:13:23
|
| One idea would be to request that the table be replicated (to your local server). Then your entire query is local. Another idea is - if the data on the local side you need to "pair up" is pretty small, you could pass that data to an SP on your remote server so that all the work is done remotely.Be One with the OptimizerTG |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-01 : 16:46:08
|
| [code]SELECT *FROM OPENQUERY(TheLinkedServer, 'Your Query')[/code]and have "Your Query" include a "massive" IN list of all the IDs to be "joined"?The IN list would be pretty horrific, but a lesser evil than a JOIN across servers I expect. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-02-01 : 17:32:13
|
| How often does the data (Unit ID's) change? How ofeten does this join need to happen?Is this somethng that you could create a local table and run SSIS (or somethjing similar) to load/update the delta's once a day or something? |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-02-02 : 02:59:48
|
| Are you on 2008? I have never tried this before and I don't know if it will perform but if you are then a slightly modified version of Kristens method using table-valued parameters might work...? -> http://msdn.microsoft.com/en-us/library/bb510489.aspx- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
PagZillA
Starting Member
4 Posts |
Posted - 2010-02-02 : 07:45:39
|
| the table-valued parameters is an interesting thing...if I can select a recordset in query, and dump it in a table-variable directly in the query, this should stop the query from going back and forth servers... Thanks LumbagoI like it! I'm going to try and I will let everyone know how it works out.Never let formal education get in the way of your learning - Mark Twain |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-02-02 : 08:04:15
|
It would be *really* interesting to see if it works so I'll be looking forward to an update  - LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
PagZillA
Starting Member
4 Posts |
Posted - 2010-02-02 : 08:58:13
|
| I found out the hard way I'm using SQL Server 2005.....Whoops >.< is there a solution similar to this via SQL Server 2005? (table variables or something?)Never let formal education get in the way of your learning - Mark Twain |
|
|
|
PagZillA
Starting Member
4 Posts |
Posted - 2010-02-02 : 10:48:50
|
| Well I used a table variable, took 15 minutes to query, I think a straight join over the physical table would take much longer.. can't prove that though, but yah... if anyone knows a faster way, let us know! (maybe 2008 is worth an upgrade for someone linking a lot of data)Never let formal education get in the way of your learning - Mark Twain |
|
|
|
|
Linked Server Queries