Entity Framework is Too Slow What are my options

Question

I have followed the  Don t Optimize Prematurely  mantra and coded up my WCF Service using Entity Framework   However  I profiled the performance and Entity Framework is too slow    My app processes 2 messages in about 1 2 seconds  where the  legacy  app that I am re-writing does 5-6 messages in the same time    The legacy app calls sprocs for its DB Access    My profiling points to Entity Framework taking the bulk of the time per message   So  what are my options      Are there better ORMs out there   Something that just supports normal reading and writing of objects and does it fast      Is there a way to make Entity Framework faster   Note  when I say faster I mean over the long run  not the first call    The first call is slow  15 seconds for a message   but that is not a problem   I just need it to be fast for the rest of the messages   Some mysterious 3rd option that will help me get more speed out of my service    NOTE  Most of my DB interactions are Create and Update   I do very very little selecting and deleting

User · Answer

This is simple non-framework  non-ORM option that loads at 10 000 second with 30 fields or so   Running on an old laptop  so probably faster than that in a real environment   https   sourceforge net projects dopersistence  source directory

User · Answer

You say that you have profiled the application   Have you profiled the ORM too   There is an EF profiler from Ayende that will highlight where you can optimise your EF code   You can find it here   http   efprof com   Remember that you can use a traditional SQL approach alongside your ORM if you need to to gain performance   If there a faster better ORM   Depending on your object data model  you could consider using a one of the micro-ORMs  such as Dapper  Massive or PetaPoco   The Dapper site publishes some comparitive benchmarks that will give you an idea how they compare to other ORMs   But it s worth noting that the micro-ORMs do not support the rich feature set of the full ORMs like EF and NH     You may want to take a look at RavenDB   This is a non-relational database  from Ayende again  that lets you store POCOs directly with no mapping needed   RavenDB is optimised for reads and makes the developers life a whole lot easier by removing need to manipulate schema and to map your objects to that schema   However  be aware that this is a significantly different approach to using an ORM approach and these are outlined in the product s site

User · Answer

One suggestion is to use LINQ to Entity Framework only for single-record CRUD statements     For more involved queries  searches  reporting  etc  write a stored procedure and add it to the Entity Framework model as described on MSDN   This is the approach I ve taken with a couple of my sites and it seems to be a good compromise between productivity and performance   Entity Framework will not always generate the most efficient SQL for the task at hand   And rather than spending the time to figure out why  writing a stored procedure for the more complex queries actually saves time for me   Once you re familiar with the process  it s not too much of a hassle to add stored procs to your EF model   And of course the benefit of adding it to your model is that you get all that strongly typed goodness that comes from using an ORM

User · Answer

You should start by profiling the SQL commands actually issued by the Entity Framework  Depending on your configuration  POCO  Self-Tracking entities  there is a lot room for optimizations  You can debug the SQL commands  which shouldn t differ between debug and release mode  using the ObjectSet lt T gt  ToTraceString   method  If you encounter a query that requires further optimization you can use some projections to give EF more information about what you trying to accomplish   Example   Product product   db Products SingleOrDefault p   gt  p Id    10      executes SELECT   FROM Products WHERE Id   10  ProductDto dto   new ProductDto    foreach  Category category in product Categories     executes SELECT   FROM Categories WHERE ProductId   10       dto Categories Add new CategoryDto   Name   category Name         Could be replaced with   var query   from p in db Products             where p Id    10             select new                               p Name                  Categories   from c in p Categories select c Name                ProductDto dto   new ProductDto    foreach  var categoryName in query Single   Categories     Executes SELECT p Id  c Name FROM Products as p  Categories as c WHERE p Id   10 AND p Id   c ProductId       dto Categories Add new CategoryDto   Name   categoryName         I just typed that out of my head  so this isn t exactly how it would be executed  but EF actually does some nice optimizations if you tell it everything you know about the query  in this case  that we will need the category-names   But this isn t like eager-loading  db Products Include  Categories    because projections can further reduce the amount of data to load

User · Answer

We have an similar application   Wcf -  EF -  database  that does 120 Requests per second easily  so I am more than sure that EF is not your problem here  that being said  I have seen major performance improvements with compiled queries

User · Answer

The Entity Framework should not cause major bottlenecks itself  Chances are that there are other causes  You could try to switch EF to Linq2SQL  both have comparing features and the code should be easy to convert but in many cases Linq2SQL is faster than EF

User · Answer

If you re purely fetching data  it s a big help to performance when you tell EF to not keep track of the entities it fetches  Do this by using MergeOption NoTracking  EF will just generate the query  execute it and deserialize the results to objects  but will not attempt to keep track of entity changes or anything of that nature  If a query is simple  doesn t spend much time waiting on the database to return   I ve found that setting it to NoTracking can double query performance   See this MSDN article on the MergeOption enum   Identity Resolution  State Management  and Change Tracking  This seems to be a good article on EF performance   Performance and the Entity Framework

User · Answer

I have found the answer by  Slauma here very useful for speeding things up  I used the same sort of pattern for both inserts and updates - and performance rocketed

User · Answer

The fact of the matter is that products such as Entity Framework will ALWAYS be slow and inefficient  because they are executing lot more code   I also find it silly that people are suggesting that one should optimize LINQ queries  look at the SQL generated  use debuggers  pre-compile  take many extra steps  etc  i e  waste a lot of time   No one says - Simplify   Everyone wants to comlicate things further by taking even more steps  wasting time    A common sense approach would be not to use EF or LINQ at all   Use plain SQL   There is nothing wrong with it   Just because there is herd mentality among programmers and they feel the urge to use every single new product out there  does not mean that it is good or it will work   Most programmers think if they incorporate every new piece of code released by a large company  it is making them a smarter programmer  not true at all   Smart programming is mostly about how to do more with less headaches  uncertainties  and in the least amount of time   Remember - Time   That is the most important element  so try to find ways not to waste it on solving problems in bad bloated code written simply to conform with some strange so called  patterns   Relax  enjoy life  take a break from coding and stop using extra features  code  products   patterns    Life is short and the life of your code is even shorter  and it is certainly not rocket science   Remove layers such as LINQ  EF and others  and your code will run efficiently  will scale  and yes  it will still be easy to maintain   Too much abstraction is a bad  pattern    And that is the solution to your problem

User · Answer

I ran into this issue as well   I hate to dump on EF because it works so well  but it is just slow   In most cases I just want to find a record or update insert   Even simple operations like this are slow  I pulled back 1100 records from a table into a List and that operation took 6 seconds with EF   For me this is too long  even saving takes too long     I ended up making my own ORM   I pulled the same 1100 records from a database and my ORM took 2 seconds  much faster than EF  Everything with my ORM is almost instant  The only limitation right now is that it only works with MS SQL Server  but it could be changed to work with others like Oracle   I use MS SQL Server for everything right now     If you would like to try my ORM here is the link and website   https   github com jdemeuse1204 OR-M-Data-Entities  Or if you want to use nugget   PM  Install-Package OR-M DataEntities  Documentation is on there as well

User · Answer

From my experience  the problem not with EF  but with ORM approach itself   In general all ORMs suffers from N 1 problem not optimized queries and etc  My best guess would be to track down queries that causes performance degradation and try to tune-up ORM tool  or rewrite that parts with SPROC

User · Answer

It only makes sense to optimize after you ve profiled  If you find out that the DB access is slow  you can convert to using stored procedures and keep EF  If you find out that it s the EF itself that s slow  you may have to switch to a different ORM or not use an ORM at all

User · Answer

I used EF  LINQ to SQL and dapper  Dapper is the fastest   Example  I needed 1000 main records with 4 sub records each  I used LINQ to sql  it took about 6 seconds  I then switched to dapper  retrieved 2 record sets from the single stored procedure and for each record added the sub records  Total time 1 second   Also the stored procedure used table value functions with cross apply  I found scalar value functions to be very slow    My advice would be to use EF or LINQ to SQL and for certain situations switch to dapper

[.net] Entity Framework is Too Slow. What are my options?

Examples related to .net

Examples related to performance

Examples related to entity-framework

Examples related to orm