What is the performance overhead of Entity Framework compared to raw SQL execution time?

Question

In my application (EF6 + SQL Server) I am dynamically creating EF queries to enable rich search functionality.

These queries are created by chaining a bunch of Where() predicates, and by projecting the results using few aggregations into a known CLR types. In all the cases EF generates a single SQL query that returns small amount of results (about 10).

Using SQL Profiler I can see that the execution time of these generated queries when executed by the database is withing few milliseconds. However, unless the query is trivially simple, the total execution time (calling ToList() or Count() from my code) is within few HUNDRED milliseconds! The code is built in Release mode and tested without debugger attached.

Can anyone give me any hints what might be wrong with my approach? Can it be possible that the EF's overhead is two orders of magnitude in time compared to the raw SQL execution time?

EDIT:

These are some code samples that I am using to filter the result set:

if (p.PriceMin != null)
    query = query.Where(a => a.Terms.Any(t => t.Price >= p.PriceMin.Value));

if (p.StartDate != null && p.EndDate != null)
    query = query.Where(a => a.Terms.Any(t => t.Date >= p.StartDate.Value && t.Date <= p.EndDate.Value));

if (p.DurationMin != null)
    query = query.Where(a => a.Itinerary.OfType<DayElement>().Count() > p.DurationMin.Value - 2);

if (p.Locations != null && p.Locations.Count > 0)
{
    var locs = p.Locations.Select(l => new Nullable<int>(l)).ToList();
    query = query.Where(a => a.Itinerary.OfType<MoveToElement>().Any(e => locs.Contains(e.LocationId)) ||
        a.Itinerary.OfType<StartElement>().Any(e => locs.Contains(e.LocationId)) ||
        a.Itinerary.OfType<EndElement>().Any(e => locs.Contains(e.LocationId)));
}

Then I order the results like this:

if (p.OrderById)
    query = query.OrderBy(a => a.Id);
else if (p.OrderByPrice)
    query = query.OrderByDescending(a => a.Terms.Average(t => t.Price));

The execution time is roughly the same if I try to execute the same query multiple times in a row (calling multiple query.Count() withing the same DbContext), so I guess in this case the EF's query generation is pretty efficient. It seems that something else is the bottleneck...

I'm seeing the same thing here. And I'm using raw SQL queries (`Database.SqlQuery<>`) that almost always return only a single row. — Cameron, Jul 07 '14 at 20:04
@Cameron Did you figured out what was the problem in your case? — Milos Mrdovic, Feb 12 '15 at 08:52
No, sorry. I suspect EF simply has very high overhead. I ended up ditching EntityFramework in favour of Dapper (which I highly recommend. Best ORM (for lack of a better term) that I've ever used). Even then, the raw SQL was still too slow (though now the bottleneck was the server and not the client), and I ended up ditching SQL Server for SQLite (which is great unless you have high-churn data), then finally ditching that for a specialized embedded SQL engine that I wrote myself. — Cameron, Feb 12 '15 at 20:09

score 5 · Answer 1 · edited May 23 '17 at 12:02

5

In general, yes EF is slower than raw SQL because it's difficult to predict how EF will build up a query and EF has no knowledge of your database indexes or how it is structured.

There's no way to say exactly what the overhead is, as it will vary from query to query. If you want to optimize your query with EF you will have to try out various ways of chaining your where predicates and benchmark the results. Even the slightest difference can make a big difference.

I ran into an issue myself where there was a huge difference between using .Any() and .Contains() which you can see here: Check if list contains item from other list in EntityFramework

The result was the same, but the second query was about 100 times faster. So yes, it is possible that for certain queries EF is two orders of magnitude slower than raw SQL. Other times it will be a few milliseconds slower.

edited May 23 '17 at 12:02

Community

1
1

answered Mar 06 '14 at 12:10

Kenneth

28,294
6
61
84

1

It seems that the problem here is not in building the SQL query. EF builds these queries rather quickly (few milliseconds at most). The queries built by EF, although complex, are also very fast when executed directly from SSMS. However, when EF itself is executing these queries, it is 100 times slower - which doesn't make much sense... – Milos Mrdovic Mar 07 '14 at 20:00
I found that EF has most of its overhead with entity tracking. Try using things like AsNoTracking when you are just doing reads. – jocull May 14 '14 at 16:30
@jocull I have tried AsNoTracking with both ProxyCreationEnabled and AutoDetectChangesEnabled set to false, but the result was the same... – Milos Mrdovic Feb 12 '15 at 08:42

score 0 · Answer 2 · answered Oct 26 '15 at 20:12

According to documentation it depends if you run a unique query for the first time or for the seconds time. It depends on your benchmark code. You should

execute the query for the first time
start stopwatch
execute the query for the second time
stop stopwatch

What is the performance overhead of Entity Framework compared to raw SQL execution time?

2 Answers2