CODEWITHSUNDEEP
c#performanceoracle-databasebulkinsertodp.net
user2243353
user2243353

Reputation: 33

Which is the most efficient way to bulk insert to a Oracle database?

As a part of my requirement in office, Im required to write an C# console application that pulls bulk data from an Oracle CRM ON Demand server and pushes it to a local Oracle DB.

Now, after much research I decided to use Array Binding feature of ODP.NET which from my understanding gives the best performance.

But here's the problem, The data I'm required to pull has a total of over 50 fields/columns per record/row. So I'm required to create that many string arrays?

Can I achieve the same(Array Binding) using a data table with 50+ columns, instead of string arrays? If so, how? (I have done enough research on this and couldn't find a solution) I know I could use OracleBulkCopy class, but from what I've read here its not so efficient and the performance is poor when compared to Array Binding.

Also, Will my application be still efficient if I use so many arrays? Because when i discussed this option with my colleagues, they claim its not a good design to have so many arrays in your application.

The above concerns have had me stuck for the past few days, can anyone please help me with the best solution approach for my problem? I need this fixed as soon as possible.

Upvotes: 2

Views: 2111

Answers (1)

schglurps
schglurps

Reputation: 1387

But here's the problem, The data I'm required to pull has a total of over 50 fields/columns per record/row. So I'm required to create that many string arrays?

Unfortunalely, yes. I had exactly the same problem, and I didn't find a better solution than the one you suggested. A DataTable is an option only if you want to use classic ADO.NET.

Dont't worry about performance : my application inserts 20 millions rows par days with Array Binding, and I dont't have performance or memory problem. But I agree with your colleagues, the code is not pretty.

Upvotes: 1

Related Questions