Entity Framework insert performance

[Deefken]

Hi,

Our purpose is to develop a console application that moves (migrates) data from an old Oracle schema to a new Oracle schema. To this end, we made an OldObjectContext with all tables that need to be moved and a NewObjectContext with all tables that have to be filled. Eventually, NewObjectContext.SaveChanges() is called to insert all data in the new schema.

The problem we face with that approach is that every record is inserted by means of one INSERT statement (this can be seen by using the OracleMonitor). Since the NewObjectContext contains +/- 180.000 records, this operation is very slow.

To fix that problem, we tried to build a ‘generic’ solution that reads out the ObjectContext and that loads all objects by means of an OracleLoader. This solution works for tables that do not have foreign keys to other tables. However, rows that belong to tables with foreign keys cannot be inserted since System.Data.Objects.ObjectStateEntry.CurrentValues contains NULL values for columns that participate in the foreign key. Is there a way to solve this problem?

We realize that this approach is not ideal in any way, but it boosts the performance of a save operation (eg. 60.000 records are saved in 3 seconds instead of 23 seconds). Can you suggest any alternatives for performing such batch inserts that work with foreign key columns or – even better – do you plan to build such optimizations into DevArt’s Entity Framework? I think that such an optimization would also benefit other situations, even CRUD applications?

Since it is not possible to attach files tot this post we can send you via email a test case application (Visual Studio 2008 project) that illustrates our problem and the 'generic' solution we made for it.

Thanks in advance for your answer,
David

PS: a problem with OracleLoader is that it has some prerequisites (not all column types are supported, transactions cannot be used, …). An alternative would be to use OracleCommand.ExecuteArray().

PPS: We are using dotConnect for Oracle v5.0.40 in combination with the Entity Framework.

[AndreyR]

The solution for the first problem is to call the EntityReference.Load() method.
As an alternative, you can use the Include() method to populate the collections.
We are unable to increase the performance of Microsoft Entity Framework significantly.
It is an ORM, and has all ORM shortcomings, namely
it wll never be as fast as plain ADO.NET.
As for the transactions and OracleLoader - I recommend you to look at the TransactionScope implementation. It allows to start a distributed transaction on the connection and then call Loader on it.

[Louis_Kleiman]

Have you tried removing the .NET piece entirely and executing some INSERT INTO...SELECT FROM statements? These work very well for me when moving data.