[HttpGet("{id}/client-history-sw01-module")]
[Authorize(AuthenticationSchemes = "AdminSchema")]
public async Task<ActionResult<object>> GetClientHistoriesSw01Module(
    long id,
    [FromServices] IMapper mapper,
    string? name,
    DateTime? startDate,
    DateTime? endDate,
    int page = 1,
    int pageSize = 10)
{
    var sw01Module = await _context.Sw01Modules
        .Include(d => d.ClientHistoriesSw01Module)
        .FirstOrDefaultAsync(d => d.Id == id);

    if (sw01Module == null)
    {
        return StatusCode(404, new { message = "Equipamento não encontrado" });
    }

    if (sw01Module.ClientHistoriesSw01Module == null || !sw01Module.ClientHistoriesSw01Module.Any())
    {
        return StatusCode(404, new { message = "Sem histórico" });
    }

    var clientHistoriesSw01Module = sw01Module.ClientHistoriesSw01Module.AsQueryable();

    if (!string.IsNullOrEmpty(name))
    {
        clientHistoriesSw01Module = clientHistoriesSw01Module
            .Where(um => um.ClientName != null && um.ClientName.Contains(name, StringComparison.OrdinalIgnoreCase));
    }

    if (startDate.HasValue)
    {
        clientHistoriesSw01Module = clientHistoriesSw01Module
            .Where(um => um.CreatedAt >= startDate.Value);
    }
    
    if (endDate.HasValue)
    {
        clientHistoriesSw01Module = clientHistoriesSw01Module
            .Where(um => um.CreatedAt <= endDate.Value);
    }

    clientHistoriesSw01Module = clientHistoriesSw01Module
        .OrderByDescending(um => um.CreatedAt);

    var totalItems = clientHistoriesSw01Module.Count();

    var totalPages = (int)Math.Ceiling((double)totalItems / pageSize);

    var paginatedClientHistoriesSw01Module = clientHistoriesSw01Module
        .Skip((page - 1) * pageSize)
        .Take(pageSize);

    var clientHistoriesSw01ModuleGetDTO = mapper.Map<IEnumerable<ClientHistorySw01ModuleGetDTO>>(paginatedClientHistoriesSw01Module);

    var result = new
    {
        Page = page,
        PageSize = pageSize,
        TotalPages = totalPages,
        TotalItems = totalItems,
        Items = clientHistoriesSw01ModuleGetDTO.ToList()
    };

    return result;
}

I'm facing a performance issue with an API endpoint where a query to retrieve data from a database is taking too long to complete. Despite applying filters and pagination, the request still takes a significant amount of time to return results.

The API endpoint retrieves client history data from a Sw01Module entity. It applies various filters (name, start date, end date) and implements pagination, but the request execution is slow.

[HttpGet("{id}/client-history-sw01-module")]
[Authorize(AuthenticationSchemes = "AdminSchema")]
public async Task<ActionResult<object>> GetClientHistoriesSw01Module(
    long id,
    [FromServices] IMapper mapper,
    string? name,
    DateTime? startDate,
    DateTime? endDate,
    int page = 1,
    int pageSize = 10)
{
    var sw01Module = await _context.Sw01Modules
        .Include(d => d.ClientHistoriesSw01Module)
        .FirstOrDefaultAsync(d => d.Id == id);

    if (sw01Module == null)
    {
        return StatusCode(404, new { message = "Equipamento não encontrado" });
    }

    if (sw01Module.ClientHistoriesSw01Module == null || !sw01Module.ClientHistoriesSw01Module.Any())
    {
        return StatusCode(404, new { message = "Sem histórico" });
    }

    var clientHistoriesSw01Module = sw01Module.ClientHistoriesSw01Module.AsQueryable();

    if (!string.IsNullOrEmpty(name))
    {
        clientHistoriesSw01Module = clientHistoriesSw01Module
            .Where(um => um.ClientName != null && um.ClientName.Contains(name, StringComparison.OrdinalIgnoreCase));
    }

    if (startDate.HasValue)
    {
        clientHistoriesSw01Module = clientHistoriesSw01Module
            .Where(um => um.CreatedAt >= startDate.Value);
    }
    
    if (endDate.HasValue)
    {
        clientHistoriesSw01Module = clientHistoriesSw01Module
            .Where(um => um.CreatedAt <= endDate.Value);
    }

    clientHistoriesSw01Module = clientHistoriesSw01Module
        .OrderByDescending(um => um.CreatedAt);

    var totalItems = clientHistoriesSw01Module.Count();

    var totalPages = (int)Math.Ceiling((double)totalItems / pageSize);

    var paginatedClientHistoriesSw01Module = clientHistoriesSw01Module
        .Skip((page - 1) * pageSize)
        .Take(pageSize);

    var clientHistoriesSw01ModuleGetDTO = mapper.Map<IEnumerable<ClientHistorySw01ModuleGetDTO>>(paginatedClientHistoriesSw01Module);

    var result = new
    {
        Page = page,
        PageSize = pageSize,
        TotalPages = totalPages,
        TotalItems = totalItems,
        Items = clientHistoriesSw01ModuleGetDTO.ToList()
    };

    return result;
}

I'm facing a performance issue with an API endpoint where a query to retrieve data from a database is taking too long to complete. Despite applying filters and pagination, the request still takes a significant amount of time to return results.

The API endpoint retrieves client history data from a Sw01Module entity. It applies various filters (name, start date, end date) and implements pagination, but the request execution is slow.

• c#
• postgresql

• 6 Your next step is to narrow down and identify the performance problem. Profiling tools can help with that. Is the performance bottleneck occurring in the database or in application code? If the database, what is the actual runtime query which is running slowly? With tools like EXPLAIN you can then also find the exact query execution plan in the database and find where it may be slowing down. – David Commented Mar 24 at 17:00
• 3 To get the complete query plan from the database, you need explain(analyze, verbose, buffers, settings) for your SQL statement. Not just explain. – Frank Heikens Commented Mar 24 at 17:15
• In fact, when querying the database using pgAdmin, the result comes quickly, but this request specifies that it is slow. – Daniel Commented Mar 24 at 17:20
• I'm not familiar with the postgresql provider, but when you do var totalItems = clientHistoriesSw01Module.Count(); you may actually be evaluating the entire query. Try eliminating that to see if it helps. Related, though not duplicate: Entity Framework Core count does not have optimal performance. – dbc Commented Mar 24 at 19:20
• Please show us the source code for the ClientHistoriesSw01Module property. – mjwills Commented Mar 24 at 22:17

1 Answer 1

var sw01Module = await _context.Sw01Modules
        .Include(d => d.ClientHistoriesSw01Module)
        .FirstOrDefaultAsync(d => d.Id == id);

This eagerly fetches the entirety of the navigation property clienthistoriesSw01Module, so all your filters and pagination after this is done in c# not sql. Move your filters into sql by using a filtered include or select operator.