Slow linked server (SQL Server to Oracle)

Question

I need to move data from an Oracle database to SQL Server.

If I run this very simple directly on the database, I get my results in < 1 second (returns 30K out of 250m rows):

select * 
From my_table             
where my_date = to_date('2024-09-17', 'YYYY-MM-DD')

But if it takes 1.5 minutes to run the exact same query using openquery / linked server

select * 
FROM OPENQUERY(ORACLE_SERVER, 'select *
                               from my_table             
                               where my_date = to_date(''17-09-2024'', ''DD-MM-YYYY'')') A

I tried with and without the to_date with no material changes.

I'm expecting the linked server to be slower, but not 100x.

Any idea where that could be coming from?

Thanks in advance

Are you sure you are measuring the same things? When you run the query on the database you may be measuring the time to return the first row. When you run the query on the linked server, (apart from also having network transfer time) you may be measuring the time to return the last row. — MT0
– MT0, Commented Sep 19, 2024 at 20:23

Yitzhak Khabinsky · Accepted Answer · 2024-09-19 20:57:59Z

1

There are 3 ways to execute query against a Linked Server in T-SQL:

Calling with four-part naming convention
Using OpenQuery and OpenRowSet
EXECUTE() AT [LinkedServer]

For the reference: SQL Server: Execute At LinkedServer

Please try the fastest method #3 as follows below. It guaranties that the WHERE clause will be applied on the Oracle db side.

T-SQL

EXECUTE('select * from my_table             
    where my_date = to_date(''17-09-2024'', ''DD-MM-YYYY'')') AT [ORACLE_SERVER];

edited Sep 19, 2024 at 20:57

answered Sep 19, 2024 at 20:45

Yitzhak Khabinsky

22.9k2 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Slow linked server (SQL Server to Oracle)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related