EF6 Entity framework sometimes slow query on SQL Server

Question

We have started a new project in .NET Core. We are just starting out and we are hitting a Web API endpoint to get some reference data of 8 records.

We noticed in our Angular screen that perodically (about every 10 requests) to a screen that the EF query takes about 6 to 15 seconds to run, rather than the 30ms it normally takes.

On debugging we know that we are getting right up to the .ToListAsync() and then in SQL profiler we can see it initiates the query and it take a delayed time.

So first impressions says its a SQL issue but if we run the SQL query manually in SQL itself it never delays.

Any ideas?

Answer 1

This might have to do with the connection pooling setup of efcore, It should not request a new connection on each request to db, enable connection pooling by adding this in your dependency management:

builder.Services.AddPooledDbContextFactory<DbContext>(
o => o.UseSqlServer(builder.Configuration.GetConnectionString("AppContext")));

reference :- https://docs.microsoft.com/en-us/ef/core/performance/advanced-performance-topics?tabs=with-di%2Cwith-constant