0

According to this link

https://learn.microsoft.com/pl-pl/aspnet/core/data/ef-mvc/sort-filter-page?view=aspnetcore-3.1

I created a pagination in my asp.net core with EF application.

Becouse I have huge amount of records (over 1 milion) when I'm trying to get last pages it takes enormous amount of time (over 30 sec).

Is there a way to speed this up?

Here is my Method which returns PaginatedList

        public static async Task<PaginatedList<Entry>> CreateAsync(IQueryable<Entry> source, int pageIndex, int pageSize)
        {
            var indexToGet = (pageIndex - 1) * pageSize;
            var count = await source.CountAsync();
            var items = await source.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync();
            return new PaginatedList<Entry>(items, count, pageIndex, pageSize);
        }

This line is responsible for slow performance. It seems like Skip and Take methods take a long time to proceed.

var items = await source.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync();

EDIT

My queries step by step.

  1. Getting all the records.
        public async Task<IQueryable<Entry>> GetAll()
        {
            var res = _context.Entries;
            return res;
        }
  1. Sorting.
switch (sortOrder)
            {
                case "who_desc":
                    entries = entries.OrderByDescending(r => r.Who);
                    break;
                case "Who":
                    entries = entries.OrderBy(r => r.Who);
                    break;
                case "date_desc":
                    entries = entries.OrderByDescending(r => r.DateUtc);
                    break;
                default:
                    entries = entries.OrderBy(r => r.DateUtc);
                    break;
            }
  1. Returning to the View
return View(await PaginatedList<Entry>.CreateAsync(entries.AsNoTracking(), pageNumber ?? 1, pageSize));

And here is my Entry

    public class Entry
    {
        public long Id { get; set; }
        public string Number { get; set; }
        public string Who { get; set; }
        public long Date { get; set; }
    }

Here is SQL query produced by EF Core

Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (59,703ms) [Parameters=[@__p_1='?', @__p_0='?'], CommandType='Text', CommandTimeout='30']
      SELECT "e"."Id", "e"."Date", "e"."Number", "e"."Who"
      FROM "Entries" AS "e"
      ORDER BY "e"."Date"
      LIMIT @__p_1 OFFSET @__p_0
lemon
  • 127
  • 1
  • 2
  • 15
  • Do you apply filters or joins on the IQueryable before this? Just wondering if there is something complicating the query. – juunas Jul 31 '20 at 07:53
  • Yes, before returning PaginatedList to my View I'm sorting the list. By Date property (stored as epoch) by default. In fact, disabling sorting helped reduce time to load like 4x times. But still, it takes 8s~ to load – lemon Jul 31 '20 at 08:15
  • 1
    @lemon do you disable change tracking? Also it is worth sharing the query that you are executing. Some queries do not translate in to good SQL – Anton Toshik Jul 31 '20 at 08:46
  • 1
    @lemon I'd recommend you to receive generated SQL and just analyze the query execution plan. It definitely will help. If you can, share the execution plan and query – svoychik Jul 31 '20 at 08:48
  • 3
    There is no silver bullet, you need to analyze your query and spot bottlenecks. You probably need to apply the appropriate indexes. – CodeCaster Jul 31 '20 at 09:08
  • Check out my edited post please – lemon Jul 31 '20 at 09:58
  • 2
    Generally, the higher offsets slow the query down, since the query needs to count off the first OFFSET + LIMIT records (and take only LIMIT of them). The higher is this value, the longer the query runs. You could try to apply the indexes, and you could also try to add a where clause to set the correct starting point to offset, more detail information, please check the following links: [Link 1](https://stackoverflow.com/questions/4481388/) and [Link 2](https://stackoverflow.com/questions/26625614/). – Zhi Lv Jul 31 '20 at 11:33
  • Applying correct indexes basically solved the problem! Thank you guys – lemon Jul 31 '20 at 13:18

0 Answers0