Efficient way to insert thousands of lines using EF

[kaan]

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]

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

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]

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]

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]

Is there any update regarding this feature in the meantime?

It would significantly speed up measuring data collection.

[Shalex]

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