question

Karen Rogers avatar image
Karen Rogers asked

Slow performance with Linked Server to Oracle

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?

linked-server
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
Surely you only need do this once? After you get it into SQL Server you can un-install Oracle and enjoy the better SQL Server performance ... !
1 Like 1 ·

3 Answers

·
Grant Fritchey avatar image
Grant Fritchey answered

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.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anurag avatar image
anurag answered
Grant Fritchey, could you provide syntax for your code? Regards, Anu
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

hoangtm avatar image
hoangtm answered

@anurag:

SELECT * FROM OPENQUERY(LinkedServer, 'SELECT * FROM TableA WHERE Column1=''ABC''')

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.