CODEWITHSUNDEEP
entity-frameworkentity-framework-6
onefootswill
onefootswill

Reputation: 4097

LINQ to Entities Perf Optimization

I'm experiencing some performance issues with EF and was wondering... well ... why.

The query I am running is simply: 

var procs = ctx.Procedures
    .Include(p => p.ProcedureProcedureFields.Select(ppf => ppf.ProcedureField))
    .Where(p => p.IsActive)
    .Where(p => !p.ProcedureLogbookTypes1.Any()).ToList();

So, not even passing in any parameters, which rules out a out of the issues. If I take the SQL from SQL Profiler and run it directly in SSMS, it takes less than 1s.

The EF call takes about 12s for the procs variable to be populated.

A few more things.

I have not just run the sql after the EF for comparison. I've made sure that there was no plan in cache. In fact, I've done both. I've run the SQL when a plan is in cache and when not. Any and every combination of running the 2 things yields the same results. The raw SQL is a fraction of the time of the EF query.

I'm all for using Stored Procedures where the query is uber-complex and the requirement for customizing the SQL is required for performance.

But the query above is simple. The SQL generated is simple.

I'd rather not litter my database with a million little stored procs, just because I cannot figure out how to make EF perform.

Is there a way to speed this up?

Thanks

Upvotes: 0

Views: 174

Answers (1)

ta.speot.is
ta.speot.is

Reputation: 27214

You can use AsNoTracking as described by this answer (which is sourced from this article):

Entity Framework exposes a number of performance tuning options to help you optimise the performance of your applications. One of these tuning options is .AsNoTracking(). This optimisation allows you to tell Entity Framework not to track the results of a query. This means that Entity Framework performs no additional processing or storage of the entities which are returned by the query. However, it also means that you can't update these entities without reattaching them to the tracking graph.

Upvotes: 1

Related Questions