gpt4 book ai didi

entity-framework - 使用 EF Core 记录查询持续时间

转载 作者:行者123 更新时间:2023-12-04 15:41:03 27 4
gpt4 key购买 nike

为了记录在 ASP.NET Core Web 应用程序中花费在 SQL Server 查询上的时间,我一直在使用以下代码,并订阅了所有 DiagnosticListeners在一些中间件中并使用 Observable以下。

我不确定这是性能方面的最佳解决方案,我想知道是否有更好的方法通过直接从 EFCore 捕获详细的日志记录对象来使用 ASP.NET Core Logging API?理想情况下,我希望保留通过请求执行的所有查询的总持续时间,并在中间件可以使用的请求结束时获得总持续时间(以毫秒为单位)。

public class QueryTimingObserver : IObserver<DiagnosticListener>
{
private readonly List<IDisposable> subscriptions = new List<IDisposable>();
private readonly AsyncLocal<Stopwatch> stopwatch = new AsyncLocal<Stopwatch>();
private double milliseconds = 0;

void IObserver<DiagnosticListener>.OnNext(DiagnosticListener diagnosticListener)
{
if (diagnosticListener.Name == "SqlClientDiagnosticListener")
{
IDisposable subscription = diagnosticListener.SubscribeWithAdapter(this);
subscriptions.Add(subscription);
}
}

void IObserver<DiagnosticListener>.OnError(Exception error)
{
}

void IObserver<DiagnosticListener>.OnCompleted()
{
subscriptions.ForEach(x => x.Dispose());
subscriptions.Clear();
}

[DiagnosticName("System.Data.SqlClient.WriteCommandBefore")]
public void OnCommandBefore()
{
stopwatch.Value = Stopwatch.StartNew();
}

[DiagnosticName("System.Data.SqlClient.WriteCommandAfter")]
public void OnCommandAfter(DbCommand command)
{
stopwatch.Value.Stop();
milliseconds += stopwatch.Value.Elapsed.TotalMilliseconds;
}

public double Milliseconds
{
get
{
return milliseconds;
}
}
}

最佳答案

分享我在我的项目中所做的事情。

  • 为每个传入请求创建 ApiRequest 对象。
  • 从请求中收集基本信息,如 IP 地址、用户代理、
    用户 id、referrer、UriPath、搜索类型等
  • 现在我可以在我的 Controller /服务层中获得对 ApiRequest 对象的引用
    启动/停止计时器并计算每个步骤的时间(db
    电话)
  • 将 ApiRequest 对象保存到首选日志记录数据库
    在给出回应之前。

  • ApiRequest 类:
    public class ApiRequest : IDisposable
    {
    [BsonId]
    public string Id { get; set; }
    // public string RequestId { get; set; }
    public string ClientSessionId { get; set; }
    public DateTime RequestStartTime { get; set; }
    public string LogLevel { get; set; }
    public string AccessProfile { get; set; }
    public string ApiClientIpAddress { get; set; }
    public GeoData ApiClientGeoData { get; set; }
    public string OriginatingIpAddress { get; set; }
    public GeoData OriginatingGeoData { get; set; }
    public int StatusCode { get; set; }
    public bool IsError { get; set; }
    // public string ErrorMessage { get; set; }
    public ConcurrentBag<string> Errors { get; set; }
    public ConcurrentBag<string> Warnings { get; set; }
    public long TotalExecutionTimeInMs { get; set; }
    public long? ResponseSizeInBytes { get; set; }
    public long TotalMySqlSpCalls { get; set; }
    public long DbConnectionTimeInMs { get; set; }
    public long DbCallTimeInMs { get; set; }
    public long OverheadTimeInMs { get; set; }
    public string UriHost { get; set; }
    public string UriPath { get; set; }
    public string SearchRequest { get; set; }
    public string Referrer { get; set; }
    public string UserAgent { get; set; }
    public string SearchType { get; set; }
    public ConcurrentQueue<RequestExecutionStatistics> ExecutionHistory { get; set; }
    public DateTime CreatedDate { get; set; }
    public string CreatedBy { get; set; }

    [BsonIgnore]
    public Stopwatch Timer { get; set; }

    public ApiRequest(Stopwatch stopwatch)
    {
    Id = Guid.NewGuid().ToString();
    // RequestId = Guid.NewGuid().ToString();
    Timer = stopwatch;
    RequestStartTime = DateTime.Now.Subtract(Timer.Elapsed);
    ExecutionHistory = new ConcurrentQueue<RequestExecutionStatistics>();
    ExecutionHistory.Enqueue(new RequestExecutionStatistics
    {
    Description = "HTTP Request Start",
    Status = RequestExecutionStatus.Started,
    Index = 1,
    StartTime = Timer.ElapsedMilliseconds,
    ExecutionTimeInMs = 0
    });
    Errors = new ConcurrentBag<string>();
    Warnings = new ConcurrentBag<string>();
    }

    public Task AddExecutionTimeStats(string description, RequestExecutionStatus status, long startTime, long executionTimeInMs)
    {
    int count = ExecutionHistory.Count;

    return Task.Run(() =>
    ExecutionHistory.Enqueue(new RequestExecutionStatistics
    {
    Description = description,
    Status = status,
    Index = count + 1,
    StartTime = startTime,
    ExecutionTimeInMs = executionTimeInMs
    }));
    }

    public Task UpdateExecutionTimeStats(string description, long executionTimeInMs)
    {
    return Task.Run(() =>
    {
    var stats = ExecutionHistory.FirstOrDefault(e => e.Description.ToLower() == description.ToLower());
    if (stats != null) stats.ExecutionTimeInMs = executionTimeInMs;
    });
    }

    #region IDisposable implementation

    private bool _disposed;

    // Public implementation of Dispose pattern callable by consumers.
    public void Dispose()
    {
    Dispose(true);
    GC.SuppressFinalize(this);
    }

    // Protected implementation of Dispose pattern.
    protected virtual void Dispose(bool disposing)
    {
    if (_disposed) { return; }

    if (disposing)
    {
    // Free managed objects here.
    Timer.Stop(); // Probably doesn't matter, but anyways...
    }

    // Free any unmanaged objects here.


    // Mark this object as disposed.
    _disposed = true;
    }

    #endregion
    }

    示例 Controller :
    [Route("api/[controller]")]
    [Authorize(Policy = Constants.Policies.CanAccessSampleSearch)]
    public class SampleSearchController : Controller
    {
    private readonly AppSettings _appSettings;
    private readonly ISampleSearchService _sampleService;

    public SampleSearchController(IOptions<AppSettings> appSettings, ISampleSearchService sampleSearchService)
    {
    _appSettings = appSettings.Value;
    _sampleService = sampleSearchService;
    }

    [HttpPost]
    public async Task<Response> PostAsync([FromBody]PersonRequest request)
    {
    var apiRequest = HttpContext.Items[Constants.CacheKeys.SampleApiRequest] as ApiRequest;
    await apiRequest.AddExecutionTimeStats("Sample Search Controller", RequestExecutionStatus.Started,
    apiRequest.Timer.ElapsedMilliseconds, 0);

    var result = new Response();

    if (!ModelState.IsValid)
    {
    apiRequest.StatusCode = (int)HttpStatusCode.BadRequest;
    apiRequest.IsError = true;

    foreach (var modelState in ModelState.Values)
    {
    foreach (var error in modelState.Errors)
    {
    apiRequest.Errors.Add(error.ErrorMessage);
    }
    }
    }
    else
    {
    result = await _sampleService.Search(request);
    }

    await apiRequest.AddExecutionTimeStats("Sample Search Controller", RequestExecutionStatus.Complted, 0,
    apiRequest.Timer.ElapsedMilliseconds);

    return result;
    }
    }

    示例服务:
    public class SampleSearchService : ISampleSearchService
    {
    #region Variables

    private readonly AppSettings _appSettings;
    private readonly IStateService _stateService;
    private readonly IDistrictService _districtService;
    private readonly IHttpContextAccessor _httpContextAccessor;

    #endregion Variables

    public SampleSearchService(IOptions<AppSettings> appSettings, IStateService stateService,
    IDistrictService districtService, IHttpContextAccessor httpContextAccessor)
    {
    _appSettings = appSettings.Value;
    _stateService = stateService;
    _districtService = districtService;
    _httpContextAccessor = httpContextAccessor;
    }

    public async Task<Response> Search(PersonRequest request)
    {
    var apiRequest =
    _httpContextAccessor.HttpContext.Items[Constants.CacheKeys.SampleApiRequest] as ApiRequest;

    await apiRequest.AddExecutionTimeStats("Sample Search Service", RequestExecutionStatus.Started,
    apiRequest.Timer.ElapsedMilliseconds, 0);

    var response = new Response();

    using (var db = new ApplicationDb(_appSettings.ConnectionStrings.MyContext))
    {
    var dbConnectionStartTime = apiRequest.Timer.ElapsedMilliseconds;
    await db.Connection.OpenAsync();
    apiRequest.DbConnectionTimeInMs = apiRequest.Timer.ElapsedMilliseconds - dbConnectionStartTime;

    await apiRequest.AddExecutionTimeStats("DB Connection", RequestExecutionStatus.Complted,
    dbConnectionStartTime, apiRequest.DbConnectionTimeInMs);

    using (var command = db.Command(_appSettings.StoredProcedures.GetSampleByCriteria,
    _appSettings.Timeouts.GetSampleByCriteriaTimeout, CommandType.StoredProcedure, db.Connection))
    {
    command.Parameters.Add(new MySqlParameter
    {
    ParameterName = "p_Id",
    DbType = DbType.Int64,
    Value = request.Id
    });

    try
    {
    await apiRequest.AddExecutionTimeStats("Sample DB Call", RequestExecutionStatus.Started,
    apiRequest.Timer.ElapsedMilliseconds, 0);

    response = await ReadAllAsync(await command.ExecuteReaderAsync());

    await apiRequest.AddExecutionTimeStats("Sample DB Call", RequestExecutionStatus.Complted, 0,
    apiRequest.Timer.ElapsedMilliseconds);
    }
    catch (Exception e)
    {
    apiRequest.StatusCode = (int)HttpStatusCode.InternalServerError;
    apiRequest.IsError = true;
    apiRequest.Errors.Add(e.Message);
    }
    }
    }

    apiRequest.SearchRequest = JsonConvert.SerializeObject(request);
    await apiRequest.AddExecutionTimeStats("Sample Search Service", RequestExecutionStatus.Complted, 0,
    apiRequest.Timer.ElapsedMilliseconds);
    return response;
    }

    private async Task<Response> ReadAllAsync(DbDataReader reader)
    {
    var SampleResponse = new Response();

    using (reader)
    {
    while (await reader.ReadAsync())
    {
    SampleResponse.AvailableCount = Convert.ToInt32(await reader.GetFieldValueAsync<long>(0));

    if (reader.NextResult())
    {
    while (await reader.ReadAsync())
    {
    var SampleRecord = new Record()
    {
    District = !await reader.IsDBNullAsync(1) ? await reader.GetFieldValueAsync<string>(6) : null,
    State = !await reader.IsDBNullAsync(2) ? await reader.GetFieldValueAsync<string>(7) : null
    };

    SampleResponse.Records.Add(SampleRecord);
    }
    }
    }
    }

    return SampleResponse;
    }
    }

    ApiRequests 中间件:
    public class ApiRequestsMiddleware
    {
    private readonly RequestDelegate _next;
    private readonly IApiRequestService _apiRequestService;

    public ApiRequestsMiddleware(RequestDelegate next, IApiRequestService apiRequestService)
    {
    _next = next;
    _apiRequestService = apiRequestService;
    }

    public async Task InvokeAsync(HttpContext context)
    {
    var stopwatch = Stopwatch.StartNew();
    var accessProfileName = context.User.Claims != null && context.User.Claims.Any()
    ? context.User.Claims.FirstOrDefault(c => c.Type == ClaimTypes.Name)?.Value
    : null;

    try
    {
    var request = new ApiRequest(stopwatch)
    {
    AccessProfile = accessProfileName,
    ClientSessionId = ContextHelper.GetHeadersValue(context, Constants.Headers.ClientSessionId),
    ApiClientIpAddress = context.Connection.RemoteIpAddress.ToString()
    };

    var originatingIpAddress = ContextHelper.GetHeadersValue(context, Constants.Headers.OriginatingIpAddress);
    request.OriginatingIpAddress = !string.IsNullOrWhiteSpace(originatingIpAddress)
    ? originatingIpAddress
    : context.Connection.RemoteIpAddress.ToString();

    request.UriHost = context.Request.Host.ToString();
    request.UriPath = context.Request.Path;

    var referrer = ContextHelper.GetHeadersValue(context, Constants.Headers.Referrer);
    request.Referrer = !string.IsNullOrWhiteSpace(referrer) ? referrer : null;

    var userAgent = ContextHelper.GetHeadersValue(context, Constants.Headers.UserAgent);
    request.UserAgent = !string.IsNullOrWhiteSpace(userAgent) ? userAgent : null;

    request.SearchType = SearchHelper.GetSearchType(request.UriPath).ToString();

    context.Items.Add(Constants.CacheKeys.SampleApiRequest, request);

    await _next(context);
    stopwatch.Stop();

    request.StatusCode = context.Response.StatusCode;
    request.LogLevel = LogLevel.Information.ToString();
    request.TotalExecutionTimeInMs = stopwatch.ElapsedMilliseconds;

    if (request.IsError)
    request.LogLevel = LogLevel.Error.ToString();

    if (_apiRequestService != null)
    Task.Run(() => _apiRequestService.Create(request));
    }
    catch (Exception e)
    {
    Console.WriteLine(e);
    throw;
    }
    }
    }

    public static class ApiRequestsMiddlewareExtensions
    {
    public static IApplicationBuilder UseApiRequests(this IApplicationBuilder builder)
    {
    return builder.UseMiddleware<ApiRequestsMiddleware>();
    }
    }

    ApiRequestService(将日志条目插入数据库):
    public class ApiRequestService : IApiRequestService
    {
    private readonly IMongoDbContext _context;
    private readonly IIpLocatorService _ipLocatorService;

    public ApiRequestService(IMongoDbContext context, IIpLocatorService ipLocatorService)
    {
    _context = context;
    _ipLocatorService = ipLocatorService;
    }

    public async Task<IEnumerable<ApiRequest>> Get()
    {
    return await _context.ApiRequests.Find(_ => true).ToListAsync();
    }

    public async Task<ApiRequest> Get(string id)
    {
    var filter = Builders<ApiRequest>.Filter.Eq("Id", id);

    try
    {
    return await _context.ApiRequests.Find(filter).FirstOrDefaultAsync();
    }
    catch (Exception e)
    {
    Console.WriteLine(e);
    throw;
    }
    }

    public async Task Create(ApiRequest request)
    {
    try
    {
    request.OriginatingGeoData = null; // await _ipLocatorService.Get(request.OriginatingIpAddress);

    var finalHistory = new ConcurrentQueue<RequestExecutionStatistics>();

    foreach (var history in request.ExecutionHistory.ToList())
    {
    if (history.Status == RequestExecutionStatus.Started)
    {
    var findPartner = request.ExecutionHistory.FirstOrDefault(e =>
    e.Description.ToLower() == history.Description.ToLower() &&
    e.Status == RequestExecutionStatus.Complted);

    if (findPartner != null)
    {
    var temp = history.Clone();
    temp.Status = RequestExecutionStatus.Complted;
    temp.ExecutionTimeInMs = findPartner.ExecutionTimeInMs - history.StartTime;
    finalHistory.Enqueue(temp);
    }
    else
    finalHistory.Enqueue(history);
    }
    else if (history.Status == RequestExecutionStatus.Complted)
    {
    var findPartner = request.ExecutionHistory.FirstOrDefault(e =>
    e.Description.ToLower() == history.Description.ToLower() &&
    e.Status == RequestExecutionStatus.Started);

    if (findPartner == null)
    finalHistory.Enqueue(history);
    }
    }

    request.ExecutionHistory = finalHistory;
    request.CreatedDate = DateTime.Now;
    request.CreatedBy = Environment.MachineName;
    await _context.ApiRequests.InsertOneAsync(request);
    }
    catch (Exception e)
    {
    Console.WriteLine(e);
    throw;
    }
    }
    }

    在配置方法中注册:
    public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
    {
    // Other code

    app.UseApiRequests();
    app.UseResponseWrapper();
    app.UseMvc();
    }

    示例日志输出:
    {
    "_id" : "75a90cc9-5d80-4eb8-aae1-70a3611fe8ff",
    "RequestId" : "98be1d85-9941-4a17-a73a-5f0a38bd7703",
    "ClientSessionId" : null,
    "RequestStartTime" : ISODate("2019-09-11T15:22:05.802-07:00"),
    "LogLevel" : "Information",
    "AccessProfile" : "Sample",
    "ApiClientIpAddress" : "0.0.0.0",
    "ApiClientGeoData" : null,
    "OriginatingIpAddress" : "0.0.0.0",
    "OriginatingGeoData" : null,
    "StatusCode" : 200,
    "IsError" : false,
    "Errors" : [],
    "Warnings" : [],
    "TotalExecutionTimeInMs" : NumberLong(115),
    "ResponseSizeInBytes" : NumberLong(0),
    "TotalMySqlSpCalss" : NumberLong(0),
    "DbConnectionTimeInMs" : NumberLong(3),
    "DbCallTimeInMs" : NumberLong(0),
    "OverheadTimeInMs" : NumberLong(0),
    "UriHost" : "www.sampleapi.com",
    "UriPath" : "/api/Samples",
    "SearchRequest" : null,
    "Referrer" : null,
    "UserAgent" : null,
    "SearchType" : "Sample",
    "ExecutionHistory" : [
    {
    "Description" : "HTTP Request Start",
    "Index" : 1,
    "StartTime" : NumberLong(0),
    "ExecutionTimeInMs" : NumberLong(0)
    },
    {
    "Description" : "Sample Search Controller",
    "Index" : 2,
    "StartTime" : NumberLong(0),
    "ExecutionTimeInMs" : NumberLong(115)
    },
    {
    "Description" : "Sample Search Service",
    "Index" : 3,
    "StartTime" : NumberLong(0),
    "ExecutionTimeInMs" : NumberLong(115)
    },
    {
    "Description" : "DB Connection",
    "Index" : 4,
    "StartTime" : NumberLong(0),
    "ExecutionTimeInMs" : NumberLong(3)
    },
    {
    "Description" : "Sample DB Call",
    "Index" : 5,
    "StartTime" : NumberLong(3),
    "ExecutionTimeInMs" : NumberLong(112)
    }
    ],
    "CreatedDate" : ISODate("2019-09-11T15:22:05.918-07:00"),
    "CreatedBy" : "Server"}

    关于entity-framework - 使用 EF Core 记录查询持续时间,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/57767700/

    27 4 0
    Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
    广告合作:1813099741@qq.com 6ren.com