CODEWITHSUNDEEP
c#oracle-databaseoracle11goracle-sqldeveloper
Caverman
Caverman

Reputation: 3707

What is an efficent way to insert about a million records into Oracle from C# console app?

I have a C# console app that I'm using Entity Framework to insert about a million records into an Oracle 11 DB. The first run took over 12 hours and I finally had to killed it. I'm logging the inserts for the moment so I can see what's going on and I'm sure that is slowing it down some. There were no errors, it was just taking for ever to insert that many files.

Someone suggested looking at SQL Loader for Oracle but I'm new to Oracle and I'm not sure I can run that from inside a console app and then I would have to make sure it completed successfully before moving on the next portion of the application which creates and export file.

Any suggestions on what I can look at to make the inserting happen quicker?

Upvotes: 2

Views: 1192

Answers (3)

SlimsGhost
SlimsGhost

Reputation: 2909

Use either SQL*Loader, OR .Net's Oracle Bulk Loader mechanism, which relies on the ODP.Net data provider. SQL*Loader is more "native" to Oracle, but ODP.Net is pure managed code without having to use an external process.

Here's a good post to help introduce these topics further: Bulk Insert to Oracle using .NET

Upvotes: 0

nvoigt
nvoigt

Reputation: 77295

Seriously, look into SQL*Loader. Read the turorials. It's insanely fast. Anything else is just wasted time, both runtime and yours. You can probably learn how to use it and insert all your data in the time it takes to just run any alternative solution.

You can use the Process class to start external processes from your console application.

Upvotes: 3

Luca Ghersi
Luca Ghersi

Reputation: 3321

You can't use EF for this kind of massive job. I mean, you can, but as you saw is not efficient.

The best way here is to use ODP.NET (http://www.oracle.com/technetwork/topics/dotnet/index-085163.html) and make a plain old PL-SQL insert.

Take a look at this answer: Bulk Insert to Oracle using .NET for more details or to this for a sample implementation http://www.oracle.com/technetwork/issue-archive/2009/09-sep/o59odpnet-085168.html

Obviously, you can still use EF for everything else, you just need to make a little arrangement to implement this step with plain old pl-sql. That's the way I use to work for example with SQL Server.

Hope it helps.

Upvotes: 4

Related Questions