| Author |
Topic |
|
tennis_123
Starting Member
7 Posts |
 Posted - 2011-12-12 : 16:15:13
|
| we have some old sps which use linked server to pull data from old systems, the sp query statements use lots of joins with local and other remote tables (same link server) and other local tables. and of course, the performance is very very slow.i try to use openquery () to pull the data from linked server first then join them with local data locally, but I have problem when I try to use join statement to join remote tables . something like: SELECT * FROM OPENQUERY(LINK_server1, 'SELECT t1.id, t2.name from table_1 t1 left outer join table_2 t2 on t1.id=t2.id') --(table_1 and table_2 are in the same remote server)GOerror --"Deferred prepare could not be completed.".I want to use openquery() to pull all the required data from linked server with less connection. to my understanding, with multiple joins, every row of return will require a new connection. is it ture? thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tennis_123
Starting Member
7 Posts |
Posted - 2011-12-13 : 11:53:31
|
| use linked server with join, it will result in multiple connection based on the join condition. is it right? that's why openquery are suggested. could you please focus on how to use openquery to join remote tables. it seems does not work. I need help on this. thank. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-13 : 11:59:16
|
This perhaps?SELECT *FROM OPENQUERY(LINK_server1, 'SELECT t1.id, t2.name from table_1 t1 left outer join table_2 t2 on t1.id=t2.id') AS LS1 JOIN LocalTable1 AS LT1 ON LT1.MyID = LS1.MyID |
|
|
|
tennis_123
Starting Member
7 Posts |
Posted - 2011-12-13 : 13:05:22
|
| but if we join in this way, it will still use the linked server to join local tables, and the result will be one row result from local table and one connection to linked server and one return data from remote server. I try to use openquery and generate the querystring like : select ... from remote_t1 left outer join remote t2 on ...left outer join remote t3 on ...where remote_t1.id in (@id_list_string)here I will use cursor to dynamic generate @id_list_sting to pass to the openquery(). my point is : send one query , generate the result in remote server and then, just return the result. remote server does not to communicate with local server to match the condition and then return result.the problem is : IF I canNOT use join within the Openquery() to join remote tables. the performance will not get improved. |
|
|
|
tennis_123
Starting Member
7 Posts |
Posted - 2011-12-13 : 13:09:45
|
| local table is quite small, and remote table is huge, it might not a good idea to pull all the remote data locally then join. we don't have control in remote, otherwise, I would create sps in remote to do the join . it seems not complicated, but I really have no idea on next steps. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-13 : 17:10:40
|
| My response was in reply to your " how to use openquery to join remote tables. it seems does not work" - my example should work.However, you are right, in that it will not be very efficient. Yes, passing a list of PKs that you want is certainly one way to reduce the number of rows being passed from Remote Server.You could also "duplicate" the data locally so that you can then just do a (local) JOIN for the data you want. That may be unacceptable for you, depending on the amount of data and how "current" you need the data to be for your query. |
|
|
|
tennis_123
Starting Member
7 Posts |
Posted - 2011-12-14 : 09:22:46
|
| thanks Kristen,It seems that OpenQuery() has some limitation, not just not accept parameters, it can not do complicated query as well. or maybe I just don't know how to use it. my plan is to generate much more complicated query statement use CTE. maximize the memory based performance. need more experience !Thank you for your help and great suggestions. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-14 : 09:49:40
|
All OPENQUERY does is to pass the query to the remote machine, and get the results back. You should (might be a big IF though!!) be able to do anything in OPENQUERY that you could do, directly, on the remote machine.If you want to restrict the amount of data considered then that must be included in the Query which OPENQUERY passes-through to the remote machine - so a JOIN to a local table won't help.One possible option would be to use a nested OPENQYERY to have the remote computer join back to the Source system - e.g. to join local table which is relatively small:SELECT *FROM OPENQUERY(LINK_server1, 'SELECT t1.id, t2.name from table_1 t1 left outer join table_2 t2 on t1.id=t2.id JOIN OPENQUERY(LinkBackToMyServer, ''SELECT MyID FROM MyLocalDatabase.dbo.LocalTable1'' AS LT1 ON LT1.MyID = t1.SomeID') AS LS1 |
|
|
|
|
join tables in linked server