Linked Servers and local tables join

Question

I'm having a problem joining local tables and linked server tables. I can do it just by using inner joins, but it's taking me too long to execute the query. I know there's a way to do it with OPENQUERY, but I can't get it.

Here's what I was doing in the beginning:

SELECT DISTINCT 
                local.L_ID
FROM dbo.local_table AS local
INNER JOIN [server].[db].[dbo].[TB_TEST] as ts 
    on local.L_ID = ts.L_ID
LEFT JOIN [server].[db].[dbo].[TB_EXE] as ex 
    on ts.A_ID = ex.T_ID

Now I'm trying to do this:

SELECT DISTINCT 
                local.L_ID
FROM dbo.local_table AS local 
INNER JOIN (
    SELECT * 
    FROM OPENQUERY(SERVER,'SELECT L_ID FROM  TB_TEST'
    ) ts 
    on local.L_ID = ts.L_ID
left join OPENQUERY(SERVER,'SELECT T_ID FROM  TB_EXE') ex 
    on ts.A_ID = ex.T_ID

Can you help me doing this the right way so the query runs quicker?

There is no way to improve join in linked server if absolute data volume is large. Consider reduce data volume in one side. Old version of SQL Server is bad regarding linked server join. What version of SQL Server are you using? — qxg, Oct 07 '16 at 12:59

gofr1 · Answer 1 · 2016-10-07T13:39:31.223

This kind of query's (with Linked Servers) may be slow because of bad connection of current instance to another or if on one of the servers is used older version of SQL Server. More info in this article.

I recommend you to use temp tables:

SELECT * 
INTO #ts
FROM OPENQUERY(SERVER,'SELECT L_ID FROM  TB_TEST;')

SELECT * 
INTO #ex
FROM OPENQUERY(SERVER,'SELECT T_ID FROM  TB_EXE;')

SELECT DISTINCT 
                l.L_ID
FROM dbo.local_table AS l 
INNER JOIN #ts 
    on l.L_ID = ts.L_ID
LEFT JOIN #ex 
    on ts.A_ID = ex.T_ID

DROP TABLE #ts
DROP TABLE #ex

About your query's.

You use almost right syntax. Try it like:

SELECT DISTINCT 
                local.L_ID
FROM dbo.local_table AS local 
INNER JOIN (
    SELECT * 
    FROM OPENQUERY(SERVER,'SELECT L_ID FROM  TB_TEST;')
    ) ts 
    on local.L_ID = ts.L_ID
left join (
    SELECT * 
    FROM OPENQUERY(SERVER,'SELECT T_ID FROM  TB_EXE;')
    ) ex 
    on ts.A_ID = ex.T_ID

Or:

SELECT DISTINCT
                local.L_ID
FROM dbo.local_table AS local 
INNER JOIN OPENQUERY(SERVER,'SELECT L_ID FROM  TB_TEST;') ts 
    on local.L_ID = ts.L_ID
left join OPENQUERY(SERVER,'SELECT T_ID FROM  TB_EXE;') ex 
    on ts.A_ID = ex.T_ID

Also note that you are using LEFT JOIN with third table and don't use it at all.

Thanks! I'm using the third table to show a column that only exists in it. — João Amaro, Oct 07 '16 at 13:48

Linked Servers and local tables join

1 Answers1