CODEWITHSUNDEEP
sql-serverlinked-serveropenquery
João Amaro
João Amaro

Reputation: 496

Linked Servers and local tables join

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?

Upvotes: 2

Views: 3907

Answers (1)

gofr1
gofr1

Reputation: 15977

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.

Upvotes: 1

Related Questions