why does topagedlist work so slow?

I think the problem is in how to compose your select. At the moment you do your .Select(s => new DocumentsModel …) the list is resolved into memory taking all rows from the DB. The OrderBy() and ToPagedList() are all executed on all the rows in memory. Below I have sort of your application using a local DB on my system. The table read is Courses which contains a lot of rows.

Executing the code below gives this result:


Getting list 1 took 2289 milliseconds
Getting list 2 took 46 milliseconds

Here’s the code:

using System;
using System.Diagnostics;

namespace ToPagedList
{
    class Program
    {
        static void Main(string[] args)
        {
            Stopwatch sw = new Stopwatch();

            sw.Start();

            var list1 = Repository.GetCourses1();

            sw.Stop();
            Console.WriteLine($"Getting list 1 took {sw.ElapsedMilliseconds} milliseconds");


            sw.Reset();
            sw.Start();

            var list2 = Repository.GetCourses2();

            sw.Stop();
            Console.WriteLine($"Getting list 2 took {sw.ElapsedMilliseconds} milliseconds");

            Console.ReadKey();
        }
    }
}

The difference in the code below is where you put the .Select() statement.

  • In the first list the whole table is read into the Select() before you do the sort.
  • In the second list only 15 rows of the table are read. The OrderBy() is done in the database and not in memory.

using System;
using System.Collections.Generic;
using System.Linq;

namespace ToPagedList
{
    public class Repository
    {
        public static List<DocumentsModel> GetCourses1(string school = null, string code = null, string title = null, int page = 0, int count = 15)
        {
            var courses = new DocumentModelEntities().Course;

            return courses.Where(course => string.IsNullOrEmpty(code) || course.CourseCode.Contains(code))
                                 .Where(course => String.IsNullOrEmpty(title) || course.Title.Contains(title))
                                 .Where(w => String.IsNullOrEmpty(school) || w.School == school)
                                 // From here your table is read from the DB using the where clauses
                                 .Select(s => new DocumentsModel
                                 {
                                     Code = code.Trim(),
                                     Title = s.Title
                                 })
                                 .OrderBy(o => o.Code)
                                 .Skip(page * count)
                                 .Take(count)
                                 .ToList();
        }

        public static List<DocumentsModel> GetCourses2(string school = null, string code = null, string title = null, int page = 0, int count = 15)
        {
            var courses = new DocumentModelEntities().Course;

            return courses.Where(course => string.IsNullOrEmpty(code) || course.CourseCode.Contains(code))
                             .Where(course => String.IsNullOrEmpty(title) || course.Title.Contains(title))
                             .Where(w => String.IsNullOrEmpty(school) || w.School == school)
                             .OrderBy(course => course.CourseCode)
                             .Skip(page * count)
                             .Take(count)
                             // From here your table is read from the DB using the where clauses, order by, skip and take
                             .Select(s => new DocumentsModel
                             {
                                 Code = code.Trim(),
                                 Title = s.Title
                             })
                             .ToList();
        }
    }
}

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top