How to increase database insert performance with Entity Framework

Question

I have my first project with Entity Framework and SQL Server Compact.

The database has about 15 tables which all have foreign keys to other tables. I have to read thousands of XML files and import their data to the database. The database structure mirrors the XML file structure. There is a table hierarchy with up to 5 levels. So for each record in the "top" table I have to insert one or more in the underlying tables.

I am using Entity Framework for inserting and it works fine, but the performance is very very poor :(. I think the main problem is that for most records the ID has to be read back to be used for records in underlying tables.

The other thing is that - if I know right - that Entity Framework inserts each record with a separate command.

Is there a way to increase the performance dramatically?

Thank you

Answer 1

Use SQL Compact Bulk Insert Library library to insert the data in bulks.

If you need to update any records, then use this technique:

1. Create a staging table in your database
2. Use the library to bulk insert into the staging table
3. Then execute a stored procedure to do an update by reading from the staging table and updating the target table's records.