none
Entity Framework Performance RRS feed

  • Question

  • Hi All,

    I'm currently working with a small development team that is about to venture into a large project and change over a large accounting system from an old programming language to .Net C#.

    A database already exists and it isn't pretty. Tables are quite large and there are a lot of them. Additionally there is no referential integrity. Normalization is basically at a minimum.

    However the database cannot be changed as it would involve redeveloping a huge amount of reports, modules, etc.

    Most of the new modules coming out of this company are already being developed with .NET, however DBMLs have been used up till now. We're trying to convert over to EF but we keep running into issues, mainly involving performance. These issues have never occurred with DBML.

    In fact we have a DBML model which consists of 1200 + tables and gives us no problems whatsoever.

    For some further background, we're working with WPF as our presentation layer and using MVVM as well as IOC (Ninject) and DI to keep everything testable.

    I'm going to list the main issues below. I've also attached a prototype download link at the bottom which demonstrates the issues we are experiencing along with a database backup that illustrates the two tables we are testing with.

    1. Materialization: It takes forever. A good 20 - 40 seconds is spent on the first query sent to our edmx. And I only have two tables in our model. The first time this became apparent I had over 500 tables in the model so it was more understandable. I pre-generated views which got it down some. However I don't feel that this should be an issue now, 2 tables should be instant. The demo illustrates this perfectly, 2 tables (10,000 rows in one and 300 in the other).
    2. DetectChanges: Also takes forever. If the two tables in this demo are completely empty it still takes 3 seconds to insert a single row. At it's current state, it takes 30 - 45 seconds. Again, DBML handles the same process in an instant.
    3. ObjectContext Disposed: We like to use edit in place data grids. Additional to this we'd like to keep the lifetime of our datacontexts to a minimum. However placing them in using statements on our data layer simply has us running into this exception over and over again. We have to use the Inlclude("Child") function on everything which ultimately just ruins our performance again. Again, DBML never had this issue.
    4. INotifyPropertyChanged: EF doesn't supply it out of the box. Had to modify the T4 template to add it for me, but it just seems hacky. Am I missing something? This is obviously required by MVVM, and since we use a lot of edit in place we are showing multiple entities which can all be modified in the same view, hence the objects themselves have to handle their own property changes rather than letting the ViewModel handle it, as the ViewModel is not bound to single entity, but a list of entities which can all be edited.

    I'll expand further if any feedback is required. I'm not sure if we're approaching it wrong, doing something silly, or if we're doing something completely wrong.

    It'd be great if anyone could provide some insight.

    We'd love to switch to EF at this point, however it currently seems like a huge leap back rather than forwards. As I said all the issues above are illustrated in the attached prototype, none of which are an issue when using DBML.

    Prototype Download Link: http://speedy.sh/jYpBd/Comp.V2.zip

    Wednesday, August 20, 2014 4:57 PM

Answers

  • I'm currently working with a small development team that is about to venture into a large project and change over a large accounting system from an old programming language to .Net C#.

    A database already exists and it isn't pretty. Tables are quite large and there are a lot of them. Additionally there is no referential integrity. Normalization is basically at a minimum.

    The above would stop me from using any ORM agaist that mess.

    If it were me, what I would do is look into creating an in-house Object Code Generator. The Object Code Gnerator would create a class/an object for each table that did CRUD operations with methods created in the class on a per table basis using in-line prametized t-sql and a DTO (Data Transfer Object) per table that is to be used.  Once the basic class has been created, then you can expand or change the methods to take in a collection of  DTO(s) for insert  as an example, for update or even deletion. You can make a List<T> of DTO(s) for a method that does a query. You can still use Linq agaist the collection. You can add  addtional methods to the generated/class/object as needed or use a partial class to expand fuctionality.

    I have seen in-house Object Code Generators used agaist MS SQL Server and Oracle database tables. The code generated was easily useable and changeable by the developers. And it gave consistency has to how things were done by the developers, and the code gernated had speed when it came to CRUD operations with the database, becuase the developer could make the changes in code using stright-up P-SQL or T-SQL in the classes with DTO(s) as input or output or a single DTO used as input or output.

    It's either do with OCG or do the same thing by hand and forget about the ORM in this case.

    Wednesday, August 20, 2014 6:33 PM