linked server query

Question

Post reply

linked server query

mfriessnig

Old Hand

Points: 354
More actions
December 2, 2014 at 2:12 pm

#295523

does a query on a table that is accessed through a linked sever apply the filter on the source linked server or at the target?
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

It depends on many factors.

Generally speaking, if the linked server is a SQL Server, the query CAN be executed as a pass-through query and the predicate is evaluated ad the remote server.

With complex queries and JOINs to remote tables, SQL Server tends to download the whole remote table and perform filters and JOINs locally.

-- Gianluca Sartori

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

I addition to the answer already provided, I would add that getting a collation match can make a massive difference, and using the COLLATE clause in the query doesn't seem to help...

For instance, say you have Server1 and Server2 and they have slightly different collations, but compatible enough that you personally don't care which is used, then in this situation, I have often found it to be beneficial to ensure any joins are performed using the collation of the remote server, and one way to do this is to store the local data in a temp table using the remote collation, then join from the temp table to the remote table.

For a small enough local set of data, this can sometimes help prevent those full remote table pulls.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

One more thing, don't forget that you can check where the filtering will occur by examining the Execution Plan - looking at the properties (F4) of the Remote Query operator to see whether it is parameterised or not and checking it's predicates.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);