Is the Entity Framework appropriate when all you do is insert records in bulk?

Question

My department develops software to migrate databases for our customers from their old CRM software to ours. In this process we could be inserting up do millions of rows, processed one at a time as we need to do mapping and formatting to go from their old system (which could be anything from a Relational Database to a set of flat binary files) to our MS-SQL database.

Currently we use a producer consumer model that reads in from their old system to our mapper, then use another producer/consumer model to go from that in to thread local DataTables which get batched out every 15,000 rows in a SQLBulkCopy operation to keep the number of objects held in ram down (larger tables can easy exhaust the 2 GB limit for a 32bit application if we try to hold the entire table in memory before inserting it.)

The issue is this is kind of a kludge setup to be able to generate the new rows and insert them in to the database. Recently we have had the opportunity to re-write the database to fit our needs and I have been working away at my manager at the idea to using some form of ORM.

I have been starting to look around at various ORM solutions, and I know we want to stay with a pure Microsoft stack (it was hard enough to get my manager to consider using an ORM, and I don't have a snowballs chance of convincing him of using a 3rd party library, since the main CRM app uses a 3rd party image processing library that is not very good and he has hated 3rd party libraries ever since). Comparing Linq-to-Sql and ADO.net Entity Framework I think the EF fits our needs better. However I am concerned about performance.

Most CRUD apps are Read->Update at a slow rate (only displaying one record or a set of records with a common variable at one time). However we will just be doing Create, Create, Create. Is an ORM like EF designed to do that?

My manager is obsessed with performance. When this process was done in VB6 (which is how it was done up to two years ago) it could take up to several hours to run a conversion. With our current kludge the longest ones take at most half an hour to process several Gigs of records (the slowest part is dealing with that 3rd party image library I mentioned earlier, ones that are pure text take at most 10 min). Could I potentially see a slowdown in EF versus using SqlBulkCopy, and what can I do to mitigate the running cost if it does?

Answer 1

I think I found the best of both worlds. On Microsoft's MSDN archive they have a LINQ Entity Data Reader which will allow me to turn my EF object in to a DbDataReader which can be passed in to SqlBulkInsert.

Answer 2

This is one scenario where an ORM can be quite counter-productive, at least from a performance standpoint. Especially out of the box, and following the typical usage patterns they will perform horribly at this sort of work. You would be better off using SQL bulk copy or straight ADO.NET [no entity framework] here.

If you do decide to take this dive, check out this blog post for some pointers. It is about nHibernate but the same sorts of things would apply to the Entity Framework I'd think.

Now just because you are using straight ADO.NET for a performance-intensive path in the app doesn't mean you can't use an ORM in other places where it makes sense.

Answer 3

Your case is typically solved by a class of application development tools usually referred to as ETL (Extract, Transform and Load). In SQL Server, you can use SSIS (see this link for example: SSIS Tutorial

There are free ETL tools such as a special edition of Expressior Studio

The nice thing about tools such as these is that you can build your application using drap-and-drop and not write a single line of code unless you want to change the data or validate it.

Most ORMs I know, don't support batch updates/inserts. One exception may be N-Hibernate (see: batch)

Any approach that will work on a row-by-row will be slow. ADO EF is not the problem. So either use Bulk Loader or an ETL tool but coding C#/ADO is not the best way to do it specially if you have large number of data sources.