March 28, 2024 at 4:30 pm
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.
March 28, 2024 at 4:37 pm
Duplicate reply my first one took a while to show up...
March 28, 2024 at 4:49 pm
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.
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
April 3, 2024 at 1:55 pm
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.
April 3, 2024 at 2:34 pm
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)
November 14, 2024 at 1:49 pm
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?
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply