I am in a situation my web based application having slow performance after reading data from DB (100-200 records) before displaying on the page with paging enabled. I followed the example from Microsoft: https://docs.microsoft.com/en-us/aspnet/core/data/ef-rp/sort-filter-page?view=aspnetcore-6.0#add-paging
I found that when my code loops through the data from the database, processes it and adds it into a list, it takes about half second for each record, more or less, according to the execution time I output.
The example from Microsoft does:
- Pull the data
- Process the data depends on input for sorting, filtering
- Page the data before return to the view to display
That sounds like it will cause performance issue when there is a large number of records in the DB as it pull everything first, then page it.
I am wondering what is the right approach to improve the performance? Also should I save the data in cache/session to save time, when the page is refreshed or previous/next page button is clicked?
Thank you.
UPDATE
Below is the key part of the Index Action in the Controller:
public IActionResult Index(string sortOrder, string currentFilter, string searchString, int? page)
{
// Read all raw data from DB
var allRequests = (from r in _context.Request).ToList();
// Create the ViewModel object List
List<CreateRequestViewModel> createRequestList = new List<CreateRequestViewModel>();
foreach (Request request in allRequests)
{
CreateRequestViewModel createRequest = new CreateRequestViewModel();
createRequest.RequestId = request.RequestId;
// Some customized code to assign value to the ViewModel object's properties
// Add the ViewModel object to the List
createRequestList.Add(createRequest);
}
// Return the PaginatedList using ViewModel List created above with paging to the page.
return View(PaginatedList<CreateRequestViewModel>.CreateAsync(createRequestList.AsQueryable(), page ?? 1, pageSize));
}
public static PaginatedList<T> CreateAsync(IQueryable<T> source, int pageIndex, int pageSize)
{
var count = source.Count();
var items = source.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
return new PaginatedList<T>(items, count, pageIndex, pageSize);
}
CodePudding user response:
Usually if you want pagination on the server side, you have to work with IQueryable
created from DbSet<>
public IActionResult Index(string sortOrder, string currentFilter, string searchString, int? page)
{
var query = _context.Request.AsQueryable();
// apply filters to query
...
var result = query.Select(r => new CreateRequestViewModel
{
RequestId = r.RequestId,
// ... other fields
});
// Return the PaginatedList using ViewModel List created above with paging to the page.
return View(PaginatedList<CreateRequestViewModel>.CreateAsync(result, page ?? 1, pageSize));
}