Entity Framework is Too Slow. What are my options?

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.

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.

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<T>.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 => 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.

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.

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

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.

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.

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.

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

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

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.

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.

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.

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.