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

Answer 1

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.