Entity Framework Core queries in an ASP.NET Core application targeting an Azure SQL database are running with wildly different runtimes

Question

I have a web app using code-first EF Core.

One of the controllers needs to run what should be a simple query (basically SELECT TOP 1 * FROM companies WHERE id = @id; in EF terms, var data = await _context.FindAsync<Company>(id)).

The first time it runs that request per HTTP request takes upwards of 3 seconds; subsequent calls on the same _context - even to get the same company - typically return in times like 0.0000040 seconds. Occasionally, however, a subsequent call to get a company will take multiple full seconds again.

Similar results are happening on other controllers, which makes me think this is a global configuration issue of some sort.

The company table has several NVARCHAR(MAX) columns, which some sources indicate might be less than ideal, but the model has no referenced models (ie., EF Core doesn't need to do any joins to build a company model).

I've tried bumping the EF Core and SQL NuGet packages (Microsoft.Data.SqlClient is at 6.13; all of the EntityFrameworkCore packages I'm using are at 8.0.22), as well as setting the app context switches recommended here; namely:

AppContext.SetSwitch("Switch.Microsoft.Data.SqlClient.UseCompatibilityAsyncBehaviour", false); 
AppContext.SetSwitch("Switch.Microsoft.Data.SqlClient.UseCompatibilityProcessSni", false);

As suggested in that same link, I've tried switching to the sync calls, as well. Further, I've added and (I believe) registered an optimized DB context (Optimize-DbContext in the package manager console), plus registering it via

return services.AddDbContext<AppDbContext>
    (options => 
        options.UseSqlServer(connectionString!)
               .UseModel(AppDbContextModel.Instance));

Nothing I've tried has had any meaningful impact on that initial query's runtime. What am I missing?

"several NVARCHAR(MAX) columns" leaves much room for vast differences in record size. Did you take that into account? Also, what if you try the non-async call? — Gert Arnold
– Gert Arnold, Commented Nov 14 at 18:53
@GertArnold: using the non-async calls doesn't have any apparent effect. Currently, the table only has 19 entries; SELECT * FROM companies in SSMS does take 11 seconds to run; the whole result set copy-pasted into a text file, is just under 1 MB. Getting just the one company in SSMS takes less than a second. — minnmass
– minnmass, Commented Nov 14 at 19:06
It sounds like the fast runs are served by SQL from memory cache, and the slow runs occur when the cache is not hit (for whatever reason). And if it takes 1 sec even from SSMS then it seems like you have chosen a very limited (cheap) instance, could that be right? Finally, do you always need the big NVARCHAR(max) field(s)? Consider doing a SELECT into a DTO class that does not have some or all of those big fields. — Peter B
– Peter B, Commented Nov 14 at 19:24
... which leads me to a final tip: in your original approach, use .AsNoTracking() to load the Company as an un-attached object. That is always the best and the fastest if you do not plan on updating the Entity and saving it back to the DB. — Peter B
– Peter B, Commented Nov 14 at 19:29
"several NVARCHAR(MAX) columns" sounds like something that should be a 1:n relationship, making it easier to get the "documents" one by one and populate an edit form gradually or on-demand. — Gert Arnold
– Gert Arnold, Commented Nov 15 at 10:07

minnmass · Accepted Answer · 2025-11-18 22:17:27Z

0

In this particular case, as suggested by several comments, the issue was the NVARCHAR(MAX) columns. After shortening many of them and pulling a couple out to other tables, the basic query is running orders of magnitude faster.

Also helping, there's another table that needs to have an NVARCHAR(MAX) column for most purposes, but there are a couple for which it's not needed; in those couple of cases, projecting the object down to not include that data is also helping tremendously.

answered Nov 18 at 22:17

minnmass

2613 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Panagiotis Kanavos Nov 19 at 7:36

Or don't load the entire object unless you really need all the properties. Which means, don't use Find. LINQ allows you to load only the columns you need in a DTO with .Select. Besides, if you store large text documents you probably want to use SqlClient's specialized streaming support instead of loading the entire string in memory.

Collectives™ on Stack Overflow

Entity Framework Core queries in an ASP.NET Core application targeting an Azure SQL database are running with wildly different runtimes

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related