Efficient way to insert thousands of lines using EF

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
kaan
Posts: 3
Joined: Mon 09 Jan 2012 12:55

Efficient way to insert thousands of lines using EF

Post by kaan » Mon 09 Jan 2012 13:17

Hi!

I am looking for an efficient way to insert thousands of lines into tables of an Oracle DB using only the EF syntax.
What I do not want is to have some OracleCommand with a plain SQL statement.

I stumbled upon a promising solution using stored procedures with table parameters which could do the job.

I found a thread here (http://www.devart.com/forums/viewtopic. ... table+type) covering this aspect. The negative reply is quite old, so maybe there was some improvement in the meantime.

Are there any good news regarding the usage of self-defined types in EF?
Do you have any hints for me how I could accomplish my task?

Thanks a lot for your help!

With best regards,

KAAN

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 11 Jan 2012 14:18

kaan wrote:I am looking for an efficient way to insert thousands of lines into tables of an Oracle DB using only the EF syntax.
We recommend you to configure EF-provider in the following way:
1) turn on and set Batch Updates
http://www.devart.com/blogs/dotconnect/ ... html#Batch
2) set InsertNullBehaviour
http://www.devart.com/blogs/dotconnect/ ... l#OtherDML
kaan wrote:Are there any good news regarding the usage of self-defined types in EF?
Only primitive data types can be used in current versions of Entity Framework. There is no way to use database-specific User Defined Types.

kaan
Posts: 3
Joined: Mon 09 Jan 2012 12:55

Post by kaan » Thu 12 Jan 2012 09:23

Thanks a lot for your fast reply!

Your hints provided an performance improvement of about 25%:

Code: Select all

      OracleEntityProviderConfig config = OracleEntityProviderConfig.Instance;
      config.DmlOptions.BatchUpdates.Enabled = true;
      config.DmlOptions.BatchUpdates.AsynchronousBatch = true;
Still, the same job is performed more than six times faster using dotConnect and ArrayBinding.

I am wondering, why EF is not using the ArrayBinding internally when ApplyChanges() is called. Is this due to the EF layer preventing that kind of optimization or is it a feature not yet implemented on your side or is that just a matter of configuration?

Thank you very much!

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 25 Jan 2012 15:03

Thank you for your suggestion. We will investigate the possibility of implementing EF Batch Updates in Oracle basing on Array Binding. But there is no timeframe at the moment.

kaan
Posts: 3
Joined: Mon 09 Jan 2012 12:55

Re: Efficient way to insert thousands of lines using EF

Post by kaan » Mon 03 Sep 2012 05:32

Is there any update regarding this feature in the meantime?

It would significantly speed up measuring data collection.

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Efficient way to insert thousands of lines using EF

Post by Shalex » Mon 03 Sep 2012 14:52

We are investigating the issue. There is no timeframe at the moment.

Post Reply