0

Very strange problem happening with the Entity Framework for our ASP.NET web application hosted on IIS. We have a simple query that continues to run slower the longer the application is running. The issue so far cannot be replicated when debugging locally through visual studio. 

var query = context.Stocktakes
    .Include(s => s.Items)
    .Include(s => s.Items.Select(i => i.Product))
    .Where(s => s.StocktakeID == stocktakeID && s.SiteID == siteID)
    .AsNoTracking()
    .SingleOrDefault();

After restarting the app pool, the query runs in less than ¼ of a second. Once there has been a small amount of traffic on the application the query performance starts to degrade, taking 6 seconds and so far up to 22 seconds after several hours of the application being hosted.

The same query when ran through SQL Server Manager takes less than ¼ of a second always. Clearing the execution plans has little to no effect so I do not believe the issue is due to parameter sniffing. I have created a copy of the application running under the same app pool, and under a different app pool with only myself connecting to it and the query performance does not degrade, it remains fast with the query taking less than ¼ of a second.

Does anyone have any ideas on what is happening?

Edit: The problem does not relate to nested projections as there is no projection to a non-entity class in the example given. The example is showing querying entities direct from the context. The link given for nested projections also does not explain why the query performance would degrade over time.

ChW
  • 3,168
  • 2
  • 21
  • 34
Tom Leeman
  • 1
  • 1
  • Maybe you are not disposing of the context properly. Are you sure it is just that one query? – Aluan Haddad Mar 29 '18 at 14:03
  • 1
    It isn't just that one query. A similar performance drop is happening with other queries that return entities. I've seen it happen with as little as 100 objects/rows returned by the query. I've put a breakpoint on the Dispose() method of the context and I can see that it is being called after each http request. – Tom Leeman Mar 29 '18 at 14:14
  • Well then at least you know the problem is general. You should profile the live app. Also, while it's not the cause of your issue, your performance will be suboptimal because you're not leveraging `async` queries. – Aluan Haddad Mar 29 '18 at 14:19
  • Check if you have some hanged requests. Go to IIS Manager - Worker Processes - select your application pool - right-click - View Requests. – M Bakardzhiev Mar 29 '18 at 14:22
  • @Aluan Haddad I thought using async queries actually slows down the execution, because of operations on worker threads being pulled and merged back with main thread. – M Bakardzhiev Mar 29 '18 at 14:28
  • @AluanHaddad Are there any specific profilers you recommend? – Tom Leeman Mar 29 '18 at 14:58
  • @MBakardzhiev There are no hung requests. Everything looks to be running okay in IIS. – Tom Leeman Mar 29 '18 at 14:58
  • is the amount of data returned roughly the same? – DevilSuichiro Mar 29 '18 at 16:48
  • @DevilSuichiro Yeah, it's roughly the same. I've ran tests with the data returned always being the same (roughly 800 records) and the performance drop still occurs. – Tom Leeman Mar 29 '18 at 18:14
  • Possible duplicate of [Entity framework nested projections are slow](https://stackoverflow.com/questions/42948326/entity-framework-nested-projections-are-slow) – Soheil Alizadeh Mar 29 '18 at 19:39
  • the only reasons I can think of that would result in such a performance loss would be connectivity issues and context size. Where does the context instance come from? (i.e. which constructor used, which queries in the same instance, model particularities) – DevilSuichiro Mar 29 '18 at 21:53
  • Do you use dependency injection that's configured by web.config settings? As suggested by others, this *does* sound like a context instance never getting disposed and gradually getting bloated ans slow. – Gert Arnold Mar 30 '18 at 07:02
  • It does sound like the context becomming bloated - but it is disposed after each http request and the change tracker shows 0 entrys. I may have stumbled on the cause for the drop in performance. The web.config contains the following setting which is used to improve the performance of SSRS reports; . Turning turn legacyCasModel off, there is no drop in the perfomance of EF. I also noticed the application is in .NET 4.6.1 and our webserver does not have that version of the .NET framework installed. I'll continue investigating and report back. – Tom Leeman Mar 30 '18 at 09:20

1 Answers1

0

The reason for the performance drop was because of the following line in the web.config file;

<trust legacyCasModel="true" level="Full" />

I assume that because Entity Framework uses dynamic data for some of its operations the LegacyCasModel setting is not compatible with the Entity Framework.

For anyone stumbling on this post who turned on this setting to improve the performance of their local SSRS reports, the following posts helped me to disable the setting above and still generate the reports fast.

Local report rdlc to pdf very slow

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6d89e2ce-3528-465f-9740-7e22aa7b7aae/slow-performance-with-dynamic-grouping-and-reportviewer-in-local-mode?forum=sqlreportingservices

Tom Leeman
  • 1
  • 1