When I run a query to select all data from a table of 71133 rows it takes 3 seconds when running from SQL 2005 Linked Server to a database on another SQL 2005 server. When I fetch the exact same data from SQL 2005 Linked to the same database on Oracle it takes 42-46 seconds. I get the same results whether linking using Microsoft OLD DB Provider for ODBC Drivers using Oracle ODBC Drivers or using Oracle Provider for OLE DB. I have tried running the query using OPENQUERY and EXEC but get the smae slow performance. Are there some settings I can change that will speed this up or do I just have to accept that it is slower form Oracle to MSSQL?
Answer by Grant Fritchey ·
To my knowledge there are no quick and easy solutions to make these type of cross server queries work well. In general, I find that OPENQUERY works best, but only if I also filter the data on the Oracle side as much as possible. WHERE clauses or JOIN clauses on the SQL side will force all the data to be retrieved from the Oracle side before the filtering occurs unless you also supply parameters to the Oracle query to eliminate as much data being moved from the source as possible.