Linq ToList() and Count() performance problem-CodePudding

I have 200k rows in my table and I need to filter the table and then show in datatable. When I try to do that, my sql run fast. But when I want to get row count or run the ToList(), it takes long time. Also when I try to convert it to list it has 15 rows after filter, it has not huge data.

public static List<Books> GetBooks()
{
    List<Books> bookList = new List<Books>();
    
    var v = from a in ctx.Books select a); 
    int allBooksCount = v.Count(); // I need all books count before filter. but it is so slow is my first problem
    if (isFilter)
        {
           v = v.Where(a => a.startdate <= DateTime.Now && a.enddate>= DateTime.Now);
        }
  .
  .
    bookList = v.ToList(); // also toList is so slow is my second problem
}

CodePudding user response：

There's nothing wrong with the code you've shown. So either you have some trouble in the database itself, or you're ruining the query by using IEnumerable instead of IQueryable.

My guess is that either ctx.Books is IEnumerable<Books> (instead of IQueryable<Books>), or that the Count (and Where etc.) method you're calling is the Enumerable version, rather than the Queryable version. Which version of Count are you actually calling?

CodePudding user response：

First, to get help you need to provide quantitative values for "fast" vs. "too long". Loading entities from EF will take longer than running a raw SQL statement in a client tool like TOAD etc. Are you seeing differences of 15ms vs. 15 seconds, or 15ms vs. 150ms?

To help identify and eliminate possible culprits:

Eliminate the possibility of a long-running DbContext instance tracking too many entities bogging down performance. The longer a DbContext is used and the more entities it tracks, the slower it gets. Temporarily change the code to:

List<Books> bookList = new List<Books>();

using (var context = new YourDbContext())
{
    var v = from a in context.Books select a); 
    int allBooksCount = v.Count(); // I need all books count before filter. but it is so slow is my first problem
    if (isFilter)
    {
       v = v.Where(a => a.startdate <= DateTime.Now && a.enddate>= DateTime.Now);
    }
    .
    .
    bookList = v.ToList();
}

Using a fresh DbContext ensures queries are not sifting through in-memory entities after running a query to find tracked instances to return. This also ensures we are running against IQueryable off the Books DbSet within the context. We can only guess what "ctx" in your code actually represents.

Next: Look at a profiler for MySQL, or have your database log out SQL statements to capture exactly what EF is requesting. Check that the Count and ToList each trigger just one query against the database, and then run these exact statements against the database. If there are more than these two queries being run then something odd is happening behind the scenes that you need to investigate, such as that your example doesn't really represent what your real code is doing. You could be tripping client side evaluation (if using EF Core 2) or lazy loading. The next thing I would look at is if possible to look at the execution plan for these queries for hints like missing indexes or such. (my DB experience is primarily SQL Server so I cannot provide advice on tools to use for MySQL)