very slow performance using WHERE on Oracle linked server

Question

very slow performance using WHERE on Oracle linked server

March 28, 2024 at 8:00 pm

This was removed by the editor as SPAM
March 28, 2024 at 8:01 pm

This was removed by the editor as SPAM
March 28, 2024 at 8:04 pm

This was removed by the editor as SPAM
March 28, 2024 at 8:05 pm

This was removed by the editor as SPAM
March 29, 2024 at 7:51 am

This was removed by the editor as SPAM
December 13, 2024 at 2:15 am

This was removed by the editor as SPAM

Viewing 12 posts - 31 through 41 (of 41 total)

You must be logged in to reply to this topic. Login to reply

cphite SSCarpal Tunnel Points: 4278 More actions · Answer 1

Sincerely appreciate all of the replies.

We've started down the path of creating a C# program that uses the Oracle’s 19c drivers - and performance is definitely much, much better; in some cases taking seconds instead of minutes to do the same operations.

Except for one table - because of course.

We have one specific table that we can select from, or delete from, normally; but inserts are taking hours. This is happening both via the new C# program, and when using the linked server. It's definitely "faster" using the C# job - but it's 8 hours versus over 12 hours. And this is for 450K rows. No varchar(max) or anything like that.

The oracle DBAs are taking a look from their end - unfortunately nobody on our side has access to really look at anything on the Oracle server. We just have access to the specific tables.

cphite SSCarpal Tunnel Points: 4278 More actions · Answer 2

Duplicate reply my first one took a while to show up...

frederico_fonseca SSCoach Points: 16167 More actions · Answer 3

could you supply the code that is doing that insert - including both the retrieval of data from SQL Server and the sending to Oracle.

And DDL for source columns being selected as well as destination Oracle table definition.

This reply was modified 8 months, 4 weeks ago by frederico_fonseca.

cphite SSCarpal Tunnel Points: 4278 More actions · Answer 4

The Oracle team determined that the driver was basically still doing a row by row insert instead of batch.

In the meantime, we decided to setup an Azure database with the exact same schema; our C# program pushed the entire database (all tables) in just under three minutes - compared to several hours to push to Oracle. I think we can live with three minutes 😀

So, at this point we're planning to recommend that we push our data to Azure, and that the Oracle team pull it from there.

They aren't allowed to pull directly from our internal servers (or even connect to them) for security reasons.

frederico_fonseca SSCoach Points: 16167 More actions · Answer 5

it could be other things and just seeming like that to your Oracle team. reason for me asking for the code you use was also to see if there had been any "tweaks" done that may have caused it - this alongside with the Oracle DDL as it would give me more info (some of it is referenced on link below)

OracleBulkCopy uses FastLoad which is nearly same as sqlloader - so any performance issues using it is likely related to other things.

do read this https://dba.stackexchange.com/questions/7287/what-specifically-does-oraclebulkcopy-do-and-how-can-i-optimize-its-performance as it does provide some info, and also the most common workaround (staging table created on the fly)

TryingToLearn SSCrazy Points: 2646 More actions · Answer 6

Hi, I am experimenting with migrating our SQL 2019 DBs to Azure Managed instances, we currently use OraClient to connect to Oracle on-prem via linked servers. I am interested in understanding how this gets done in Azure?