CODEWITHSUNDEEP
sql-serverperformancelinked-server
user2163043
user2163043

Reputation: 371

Linked server query takes long time unless filter is hard-coded

I have a simple select query I'm executing as below:

SELECT Id FROM [LinkedServer].[Catalog].[dbo].[someTable] WHERE date = @someDate AND Id IN (SELECT Id FROM ids)

ids is a single-column table with a list of 50 or so IDs (FWIW the IDs are all strings, not ints), and LinkedServer is a server across the Atlantic. someTable is a mapping of many (~300m) (date,ID) pairs to values and has the appropriate PK.

The curious thing that I cannot get my head around is that this query takes 10s to execute - but if I replace (SELECT Id FROM ids) with the hard-coded contents of ids, so the query reads

SELECT Id FROM [LinkedServer].[Catalog].[dbo].[someTable] WHERE date = @someDate AND Id IN ('id1','id2', ..., 'id50')

say, it executes in <1s. Can anyone explain this difference and give any ideas how I might carry over the performance of the hard-coded query into the true one?

Upvotes: 1

Views: 730

Answers (1)

pacreely
pacreely

Reputation: 1931

The first query is running in a Distributed fashion across 2 servers. This can be slow because Server A doesn't know the Statistics for Server B and a proper Execution Plan Can't be produced.

The second query "Transmits" the query to the Server B, the query then runs exclusively on Server B, and finally the results are transmitted back to Server A.

Upvotes: 1

Related Questions