How to optimise pages loading time when there's a big database request (C# ASP.NET Core 6 MVC)

Question

I'm working on a big project. The project database contains a lot of data to display on the pages. When I want to access a page, it can take up to 15 seconds to fully load, which is way too slow.

I am using C# .NET 6, it's an ASP.NET Core 6 MVC project using SQL Server.

I tried filtering Linq requests to import less data by pages; I tried to do pagination with HTML tables to display less data at the same time, but it's still very slow.

Here's an example of requests we're doing:

public IActionResult Index(int id)
{
    var factures = _context.Factures
                           .AsNoTracking()
                           .Include(x => x.Chantiers)
                           .Where(x => Convert.ToInt32(x.Annee) > 2020)
                           .Select(x => new FacturesViewModel()
                                            {
                                                // DATA HERE
                                            })
                           .ToList();

    return View(factures);
}

Does anyone know how I can improve the page load speed?

Answer 1

After an hour of work, i found a solution

I used datatables ( https://datatables.net/ ) to do the pagination (only show 10 items each time), now the page loads much faster.

The ID parameter will also be used to order invoices in the list.