Menu
What is the fastest way to do Bulk insert to Oracle using .NET? I need to transfer about 160K records using .NET to Oracle. Currently, I'm using insert statement and execute it 160K times.It takes about 25 minutes to complete. The source data is stored in a DataTable, as a result of query from another database (MySQL),
Is there any better way to do this?
EDIT : I'm currently using System.Data.OracleClient, but willing to accept solutions using another provider (ODP.NET, DevArt, etc..)
978
Upon completion of the calculations, the bulk insert will write the data from the PL/SQL array into a table far faster than a traditional cursor for loop. The syntax for a bulk insert is simpler than non-bulking SQL, and the bulk insert using a forall operator is far faster, even with small data samples.
SQL*Loader is my favorite way to bulk load large data volumes into Oracle. Use the direct path insert option for max speed but understand impacts of direct-path loads (for example, all data is inserted past the high water mark, which is fine if you truncate your table).
I'm loading 50,000 records in 15 or so seconds using Array Binding in ODP.NET
It works by repeatedly invoking a stored procedure you specify (and in which you can do updates/inserts/deletes), but it passes the multiple parameter values from .NET to the database in bulk.
Instead of specifying a single value for each parameter to the stored procedure you specify an array of values for each parameter.
Oracle passes the parameter arrays from .NET to the database in one go, and then repeatedly invokes the stored procedure you specify using the parameter values you specified.
http://www.oracle.com/technetwork/issue-archive/2009/09-sep/o59odpnet-085168.html
/Damian
198
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
