Linked server to Oracle doesn’t use filters. Are there any performance changes in different versions of SQL Server?

My customer somehow only has SQL Server 2005 instance. It has linked server to oracle database. I’ve noticed that sql server doesn’t send any filters to oracle side and apply them only after it retrieves all data from oracle. Which is quite opposite to work of sql server – sql server linked server.

I’m wondering – could it be because of sql server version? Maybe 2005 is to old for that?

For example:

SELECT * FROM OPENQUERY (ORCLLS, 'Select * from ADMIN.IE_Docs_Subscrs where F_DRSO = ''84c9e2c5-2634-4f6a-b1b7-e1505d4a1c39''')

Result: 0 seconds. 24 rows

SELECT * FROM OPENQUERY (ORCLLS, 'Select * from ADMIN.IE_Docs_Subscrs')
WHERE  F_DRSO = '84c9e2c5-2634-4f6a-b1b7-e1505d4a1c39'

Result: 1m 33 seconds. 24 rows

EDITED

Got it. It’s because of OPENQUERY. But how can I turn it into four-part name?

By default SQL Server doesn’t believe that string comparisons will have the same semantics on the linked server that they have locally. So it can’t “push the predicate” to the remote server.

In particular Oracle is always case-sensitive, and SQL Server by default is not. So in SQL Server you get the same results with

WHERE  F_DRSO = '84c9e2c5-2634-4f6a-b1b7-e1505d4a1c39'

and

WHERE  F_DRSO = '84C9e2E5-2634-4f6A-B1B7-E1505D4A1C39'

But not in Oracle.

You can set the “Collation Compatible” setting of the linked server to allow SQL Server to push predicates.

But I really prefer to use OPENQUERY or EXEC (‘sql’) AT LinkedServer and retain explicit control over the remote query.

To enable the predicate to be sent to the linked server, the query needs to use 4-part names.

For oracle this would look something like:

select * 
from ORCLLS..ADMIN.IE_Docs_Subscrs
WHERE F_DRSO = '84c9e2c5-2634-4f6a-b1b7-e1505d4a1c39'

Oracle traditionally (pre 12) has only a single database per instance, so the second part of the 4-part name is empty.