Entity Framework is Too Slow. What are my options?

Question

Closed. This question is off-topic. It is not currently accepting answers.

---

Want to improve this question? Update the question so it's on-topic for Stack Overflow.

Closed 3 years ago.

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.

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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.

Answer 6

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.