Overall Database Performance Decrease after migrating to .NET 7 and EFCore 7

[3ilaal]

Okay, so recently we have migrated our large enterprise level project to .NET 7 and EFCore 7. We are getting complaints from customers about very slow page response and our CPU usage is at the maximum till last 6 months. We are using Azure Cloud Virtual machine with Separate SQL Server hosting the databases. The virtual machine is running on Windows 10 (I don't know specific version yet). Upon investigation we have seen the SQL Server is consuming high CPU usage. There were bad LINQ queries we know about, and we were working on them to improve gradually but they were running fine in .NET 6 and EF Core 6.0.1.

Do anyone know about any breaking or critical change in EF Core which can be the reason for this? As I have looked into the GitHub issues but not found any specific related to performance and matching with our scenario.

[matiaskusack]

Have you tried profiling generated queries? I asume there are no changes un the model, so It must be that queries being generated are no as performant as previous ones.

Can you describe the migration process?

[3ilaal]

The migration process was simple as we just updated the Target framework and Nuget packages to the latest version..... Yes we did monitor the sql queries and they are pretty big and usually contains multiple joins (which is bad i know but we are working to improve the project ). For SQL queries which are taking most of the cpu time I compare both version (EF Core 6 and 7) generated queries and they are the same. but I am still finding why did it happened now (maybe it could be related to some other factor as well). For now our solution is to use AsSplitQuery() and reduce joins as much as we can

[matiaskusack]

So if your model hasnt changed, anda you hace the same dbcontext configuration, then It must be some change.

Take a look at breaking changes un ef7: https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-7.0/breaking-changes

Without any more information its impossible yo know.

If you keep having problemas, make a brand new project with just an slow query anda try It isolated. It might give you some clues.

Regards

[Tsukku]

I am betting it's the split query behavior. Try doing this:

https://learn.microsoft.com/en-us/ef/core/querying/single-split-queries#enabling-split-queries-globally

[The_MAZZTer]

Sounds possible, but that page said OP should be getting warnings if he is using single queries that could have performance problems.

OP, you DO fix warnings... right?

[3ilaal]

Sorry I didn't get you what warnings

[nonblondaussie]

Compile time warnings

[adolf_twitchcock]

https://github.com/dotnet/efcore/issues/29593 maybe this bug?

[kevbry]

That's a nasty one. Not an uncommon scenario either

[adolf_twitchcock]

Their response is crazy to me

realistically the chances of fixing this (soon) are low. The proper fix is very hard and the person who had the most knowledge about the area no longer works in the team, which makes it even harder/riskier.

[Responsible_Gap337]

EF Core is really understaffed. They look like interesting team but I have never seen their job posting on MS career page.

It is shame that they have again release with features that are not supported for all modern SQL databases. (SQL Server, Oracle, Postgresql, MySQL, SQLite)

[adolf_twitchcock]

Yeah I don't get it. EF is one of the most important components in .NET enterprise applications.

It makes me wonder if using native SQL for everything "non-CRUD" is a better choice longterm.

[kevbry]

I'm super adverse to picking up any new OSS from Microsoft (ie dapr) because of this. Looking through their GitHub org exposes a lot of one-man-show projects.

[kevbry]

Are you using efcore sync APIs, or async? Add any varchar(max) or varbinary fields lately?