Openquery works much faster than a query straight to a linked table

Question

Trying to figure out why there is such a significant difference between select * from linkedserver..tablename and select * from openquery(linkedserver, select * from tablename). 4 minutes vs 13 seconds.

Answer 1

Openquery connects directly to the destination server. Despite saying the RDBMS, you did not said what the other way to communicate with the data was.

Answer 2

Do you know if it is trying to enlist a distributed transaction over the linked server? If you watch the Trace output from Oracle when the calls are made, you should be able to see the difference in the requests happening. IIRC, the standard link query will request schema information from the oracle metadata in a different manner than the OPENQUERY call does. I don't know exactly why this happens, but I had a similar issue in the past that was resolved by using OPENQUERY.

Answer 3

OPENQUERY connects to the destination server and runs the query on that server and returns the resultset. Whereas, I believe the Linked Server query is executed on the local server and runs across the connection.

Hope this helps.