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

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.

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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.

Answer 6

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.