CODEWITHSUNDEEP
sql-serveroracle-databaseoracle11glinked-server
Georgy Smirnov
Georgy Smirnov

Reputation: 391

EXECUTE AS vs SELECT FROM linked server

I've got an Oracle server integrated with MS SQL as a linked server. Currently I'm working on the query optimization. I've found out that queries that written as following:

SELECT colName1, colName2, .. 
  FROM ORACLE.TBL_TBLENAME 
 WHERE something = @something

work very slowly. On the other hand, the same query written as:

EXECUTE ('SELECT colName1, colName2, .. 
            FROM TBL_TBLENAME 
           WHERE something :something',@something) at ORACLE

work much faster.

What I'm concerned about is the execution plan. For the first query Estimated Subtree Cost is 0.16, for the second it is 3.36. The second query performs a 'Remote scan'. I don't know whether this is good or not.

The query is supposed to run quite often (around 20 queries in 1 minute).

Upvotes: 0

Views: 971

Answers (1)

DazzaL
DazzaL

Reputation: 21993

given you execution plan (and i'm an oracle guy not a sql server guy), it appears that the first one is doing a full table scan and filtering at the sql server end (compute scalar?), whereas the 2nd one is sumitting the filter to oracle and so much quicker.

are the stats up-to-date on the oracle table (perhaps it thinks there's only a few rows in the table so sql server is deciding its better to just fetch the whole table over and do the procesing locally?) and are there any histograms involved on "something"?

if the 2nd one is performing good for you though, is there really a problem?

Upvotes: 1

Related Questions