Entity Framework Core 使用 DbCommandInterceptor 找出效能低落的查詢指令

在 Entity Framework Core 中 DbCommandInterceptor 可以用來攔截和修改程式往返資料庫的 SQL 指令、回應資訊等,我們可以使用攔截器檢查回應的時間是否超過指定的毫秒,如果超過就在 Logger 中顯示警告訊息:
    
using System.Data.Common;
using Microsoft.EntityFrameworkCore.Diagnostics;


public class QueryPerformanceInterceptor : DbCommandInterceptor
{
    private readonly ILogger<QueryPerformanceInterceptor> _logger;
    public static readonly int ThresholdMilliseconds = 2_000;

    public QueryPerformanceInterceptor(ILogger<QueryPerformanceInterceptor> logger)
    {
        _logger = logger;
        _logger.LogInformation("QueryPerformanceInterceptor initialized");
    }

    public override DbDataReader ReaderExecuted(DbCommand command, CommandExecutedEventData eventData,
        DbDataReader result)
    {
        LogCommand(eventData);
        return base.ReaderExecuted(command, eventData, result);
    }

    public override ValueTask<DbDataReader> ReaderExecutedAsync(DbCommand command, CommandExecutedEventData eventData,
        DbDataReader result,
        CancellationToken cancellationToken = new CancellationToken())
    {
        LogCommand(eventData);
        return base.ReaderExecutedAsync(command, eventData, result, cancellationToken);
    }

    private void LogCommand(CommandExecutedEventData eventData)
    {
        var elapsedMilliseconds = eventData.Duration.TotalMilliseconds;
        if (!(elapsedMilliseconds > ThresholdMilliseconds)) return;

        _logger.LogWarning(
            "Slow query: {ElapsedMilliseconds}ms\nCommand: {Command}",
            elapsedMilliseconds,
            eventData.Command.CommandText
        );
    }
}
    

註冊服務:
    
builder.Services.AddSingleton<QueryPerformanceInterceptor>();

var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
builder.Services.AddDbContext<ApplicationDbContext>((serviceProvider, options) =>
{
    var queryPerformanceInterceptor = serviceProvider.GetRequiredService<QueryPerformanceInterceptor>();
    
    options.UseSqlServer(connectionString)
        .AddInterceptors(queryPerformanceInterceptor);
});


var app = builder.Build();
    



參考資料:
Microsoft.Learn - DbCommandInterceptor Class

留言