Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?


Also I had a really hard time deciding if this should go here or SO, if you think it should be SO please let me know.


UPDATE:

In response to Wyatt Barnett answer: if using an ORM is the totally wrong way to go, is there any other solution that will perform well other than generating generic DataTables on the fly based off of the schema of the destination database? The thing I liked most about EF was the fact that I had strongly typed data types, and that I could update both our database and our objects mapping the database with the tools built into Visual Studio in one location.

share|improve this question
add comment

3 Answers

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.

share|improve this answer
    
I have used those before and I enjoy them, however I have to fight to get them to use a 3rd party library, I doubt I will have any luck switching languages. –  Scott Chamberlain Oct 6 '11 at 0:06
    
This is not a switch of language, it is using the right tool for the job. SSIS supports the C# language. –  baldy Oct 6 '11 at 7:40
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
Thanks, I updated my original question based off this answer, do you have any suggestions on how I can get the performace of SqlBulkCopy and the strong types of EF? If I loose the "update both in one location" ability it is not a dealbreaker. For example can I get Strongly Typed Data tables from EF or Linq-to-sql? –  Scott Chamberlain Oct 5 '11 at 23:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.