aabrodsky

Remote filtering with PI OLEDB Enterprise as Linked Server

Discussion created by aabrodsky on Jun 30, 2011
Latest reply on Nov 14, 2012 by alexandre.bouffard

Hi All,

 

It appears, that all queries against PI OLEDB Enterprise apply filtering on the SQL side, after pulling in the complete data set from the remote AF server.

 

Is there something with collation compatible or other tweaks to change that behaviour, or is that the standard way how MS SQL 2008 works with Linked Servers?

 

I have two queries to compare behaviour:

 

1. Direct one

 
select Value from 
RM_OLEDB.IO_RM.Data.Snapshot s
where s.ElementAttributeID = {guid'ffffffa0-ffff-ffff-9201-000000000000'}

 2. Same via OpenQuery - passthrough to OLEDB, forcing SQL to run it on the remote side:

 
select * from 
OPENQUERY(RM_OLEDB,'select * from IO_RM.Data.Snapshot s
where s.ElementAttributeID = ''ffffffa0-ffff-ffff-9201-000000000000''')

In the first case the execution plan shows full scan of remote table, bringing all results back to the SQL, applying filter on the SQL side. The query takes 16 seconds (11560 unique attributes in Snapshot)

 

5265.ExecutionDirect.png

 

 

 

 In the second case,  the query runs completely on the remote side, apllies all filtering there and brings only one resulting row. Takes less than a second.

 

2677.ExecutionPassThrough.png

 

Although OpenQuery is a nice thing to work with, you lose flexibility of JOINs with local SQL data. Additionally performance of Openquery in general is worse than direct queries, because the SQL can't compile and re-use dynamic SQL queries, when you need to change filtering parameters.

 

Is there any way to force SQL pass filtering criteria over to Linked Server instead off applying filtering locally afterwards?

 

Thanks,

 

Alex Brodskiy

Outcomes