CODEWITHSUNDEEP
performanceentity-frameworkasp.net-core.net-coreentity-framework-core
Gobelet
Gobelet

Reputation: 483

ef core slow Initialization of DbContext on a small datamodel

I think I have an issue with the initialization of my DbContext. I know a lot of people talked about it but I'm probably doing something wrong because these answers were related to big context or big tables (many columns or a lot of data).

In my case the User table is composed of 30 columns and I have only 30 000 rows.

When I start the application or when it's recycled it took 22 seconds to have the first result of the query.

I've added some logs and from what I understood apparently the query is not the problem (you can find these logs lower). When I copy paste the query in SSMS I have a result in less than one second. After the first execution the query and everything is almost "immediate". So,tThe problem would come from the initialization of the context.

I read that with some precompiled view or this kind of stuff it could be faster. I'm really surprised that with just that "little" table I would need special treatments to have acceptable performance. That's why I think I'm missing something.

My application is in .net core 3.1 and I use Entity Framework Core 5.0.8

In my ConfigureService I have:

services.AddDbContext<DbContext>(options =>
            options.UseSqlServer(serviceConfiguration.ConnectionString));

My DbContext:

public class DbContext : DbContext
{
    private readonly ILoggingService<DbContext> _loggingService;
    private readonly ILoggerFactory _loggerFactory;

    public virtual DbSet<User> Users { get; set; }

    public DbContext(DbContextOptions<DbContext> options, ILoggingService<DbContext> loggingService, ILoggerFactory loggerFactory)
        : base(options)
    {
        _loggingService = loggingService;
        _loggerFactory = loggerFactory;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.EnableSensitiveDataLogging();
        optionsBuilder.EnableServiceProviderCaching();

        optionsBuilder.UseLoggerFactory(_loggerFactory);
        base.OnConfiguring(optionsBuilder);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        _loggingService.LogError("Begin model creating");
        modelBuilder.Entity<User>(entity =>
        {
            entity.HasKey(u => u.UserId);
            entity.ToTable("User");
        });
        _loggingService.LogError("End model creating");
    }
}

2021-08-09 15:28:30.830|WARN|Microsoft.EntityFrameworkCore.Model.Validation|Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development.|url:|action:UsersController.CreateUser

2021-08-09 15:28:30.957|INFO|Microsoft.EntityFrameworkCore.Infrastructure|Entity Framework Core 5.0.8 initialized 'DbContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: SensitiveDataLoggingEnabled |url:|action:UsersController.CreateUser

2021-08-09 15:28:52.702|INFO|Microsoft.EntityFrameworkCore.Database.Command|Executed DbCommand (53ms) [Parameters=[@__username_0='' (Size = 4000)], CommandType='"Text"', CommandTimeout='30'] SELECT TOP(1) [u].[UserId], [u].[Email], [u].[Firstname], [u].[Username] FROM [User] AS [u] WHERE ([u].[Username] = @__username_0) AND ([u].[StatusId] > 0)|url:|action:UsersController.CreateUser

2021-08-09 15:28:52.973|INFO|Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker|Executed action UsersController.CreateUser in 23053.3706ms|url:|action:UsersController.CreateUser

Upvotes: 1

Views: 1757

Answers (1)

Gobelet
Gobelet

Reputation: 483

After more analysis the problem also appeared after 5-10min of inactivity even if in IIS the idle time is to 0. I've discovered the parameter Min Pool Size in the connection string and it solved my issue for the inactivity of 5-10min.

Upvotes: 1

Related Questions