5

I have some IDs inserted into a temp table #A as follows:

SELECT DISTINCT ID
INTO #A
FROM LocalDB.dbo.LocalTable1
WHERE ID NOT IN (SELECT DISTINCT ID FROM LocalDB.dbo.LocalTable2)
GO

CREATE INDEX TT ON #A(ID)
GO

I am trying to obtain some information from a remote linked server using the identifiers I gathered in the previous stage:

Query 1:

SELECT ID, Desc
FROM RemoteLinkedServer.DB.dbo.RemoteTable X
WHERE ID IN (SELECT ID FROM #A)

Query 2:

SELECT ID, Desc
FROM RemoteLinkedServer.DB.dbo.RemoteTable X
INNER JOIN #A Y
ON X.ID = Y.ID

Now in the following query, what I am doing is obtain the output of the temp table, copy the rows and format them properly into a comma-separated list and manually putting it in the query.

Query 3: 

SELECT ID, Desc
FROM RemoteLinkedServer.DB.dbo.RemoteTable X
WHERE ID IN (-- Put all identifiers here --)

Queries 1 and 2 take 2 hours to execute and query 3 takes 0 seconds (my temp table contains about 200 rows). I don't know what's going on and do not have permissions to check if the remote server has the relevant indexes on ID but it is simply baffling to see that a manually constructed query runs in no time indicating that there is something that is going wrong at the query optimization phase.

Any ideas on what's going wrong here or how I could speed up my query?

Legend
  • 113,822
  • 119
  • 272
  • 400

2 Answers2

10

Queries 1 and 2 cause ALL of the data in the RemoteTable to be pulled into your local database in order to perform the join operation. This is going to eat RAM, network bandwidth and generally be very slow while the query is executing.

Query 3 allows the remote server to filter down the results to send just those matches you want.

Basically, it boils down to who does the work. Queries 1/2 require your local DB to do it; Query 3 lets the remote one do it.

If you have a lot of data in that remote table, then you'll likely run into network congestion etc.

The best approach to querying linked servers is to construct your queries such as the remote server does all the work and just sends results back to your local one. This will optimize the amount of network, memory and disk resources required to get the data you want.

Any time you have to join across server boundaries (using a linked server) it's going to be a disaster.

NotMe
  • 87,343
  • 27
  • 171
  • 245
  • 1
    +1 Honestly, I did not know that! Thank you for the clarification. So how do I ship my local values to the remote linked server and get back the results? `CURSOR`s and then send one identifier at a time? The remote table is huge with about 10 million rows. – Legend Aug 11 '12 at 00:42
  • 2
    Probably your best bet is to dynamically construct the sql to execute against the remote server and just EXEC it. We've found that this method is much much faster for every type of call we make to linked servers. Using a cursor then running one query at a time may cause you to hit transaction speed limits; which you may not want. – NotMe Aug 11 '12 at 00:46
  • Thank you for the suggestion. There are no more bottlenecks and my script is running fine with this optimization. – Legend Aug 11 '12 at 05:52
3

For reference, this is how I solved the problem based on @ChrisLively's suggestions:

SELECT DISTINCT ID
INTO #A
FROM LocalDB.dbo.LocalTable1
WHERE ID NOT IN (SELECT DISTINCT ID FROM LocalDB.dbo.LocalTable2)
GO

CREATE INDEX TT ON #A(ID)
GO

DECLARE @IDList VARCHAR(MAX)
SELECT @IDList=(SELECT TOP 1 REPLACE(RTRIM((
                SELECT DISTINCT CAST(ID AS VARCHAR(MAX)) + ' ' 
                FROM #A AS InnerTable               
                FOR XML PATH (''))),' ',', '))
FROM #A AS OuterResults


DECLARE @sql AS varchar(MAX)
SET @sql = 'SELECT * FROM RemoteLinkedServer.RemoteDB.dbo.RemoteTable X WHERE ID IN (' + @IDList + ')'

EXEC (@sql)

DROP TABLE #A
GO
rth
  • 10,680
  • 7
  • 53
  • 77
Legend
  • 113,822
  • 119
  • 272
  • 400