Large database linq query to sql server takes forever

Question

Background

So, I am using a React frontend and a .net core 3.1 backend for a webapp where I display a view with a list of data. The list is often times several thousands long. In this case its around 7500. We virtualize it to prevent sluggishness. Along with the display of data, every row has a column with the latest logchange someone did on that datarow. The logs and the rest of the data for every row comes from two different applications with their own databases. The log data consists of the name, and date of when the log was made, is also supposed to be rendering for every row.

The problem

When you route to the page, a useEffect fires that fetches the rows from one of the databases. When I get the response, I filter out all of the ids from the data and then I post that list to the other endpoint to request the latest log from every id. This endpoint queries the logging database. The number of ids I am passing to the endpoint is about 7200+. It wont always be this much, but sometimes.

Troubleshooting

This is the query that is giving me trouble in the log endpoint

public async Task<IActionResult> GetLatestLog(ODataActionParameters parameters)
{
    var LogIds= (LogIds)parameters["LogIds"];
    var results = await context.Set<LogEvent>()
                    .Where(x => LogIds.Ids.Contains(x.Id)).ToListAsync(); //55 600 entities

    results = results
              .GroupBy(x => x.ContextId)
              .Select(x => x.OrderByDescending(p => p.CreationDate).First()).ToList(); //7 500 entities

    var transformed = results.Select(MapEntityToLogEvent).ToList();

    return Ok(transformed);
}

The first db query takes around 25 seconds (!) and returns around 56000 entities.

The second linq takes about 2 seconds, and returns around 7500 entites, and the mapping takes around 1 second.

The database is SQL server, and there are three indexes, one of which is Id, the other two are irrelevant for this assignment.

I have tried different queries, AsNoTracking, but to no avail.

Obviously this is horrible. Do you know of a way to optimize this query?

Answer 1

There are two ways, how to improve your query:

Pure EF Core

We can rewrite LINQ query to be translatable and avoid unnecessary records on the client side. Note that your GroupBy will work with EF Core 6:

public async Task<IActionResult> GetLatestLog(ODataActionParameters parameters)
{
    var LogIds = (LogIds)parameters["LogIds"];
    var results = context.Set<LogEvent>()
       .Where(x => LogIds.Ids.Contains(x.Id)); 

    results = 
        from d in results.Select(d => new { d.ContextId }).Distinct()
        from r in results
            .Where(r => r.ContextId == d.ContextId)
            .OrderByDescending(r => r.CreationDate)
            .Take(1)
        select r;

    var transformed = await results.Select(MapEntityToLogEvent).ToListAsync();

    return Ok(transformed);
}

Using third party extension

With linq2db.EntityFrameworkCore we can use full power of the SQL and make most efficient query in this case. Big list of ids can fast be copied to temorary table and used in result query. Retrieveing only latest records by ContextId can be done effectively with Windows Function ROW_NUMBER.

Disclaimer I'm maintainer of this library.

// helper class for creating temporary table
class IdsTable
{
    public int Id { get; set; }
}

public async Task<IActionResult> GetLatestLog(ODataActionParameters parameters)
{
    var LogIds = (LogIds)parameters["LogIds"];
    using var db = context.CreateLinqToDBConnection();
    TempTable<IdsTable>? idsTable = null;

    var results = context.Set<LogEvent>().AsQueryable();

    try
    {
        // avoid using temporary table for small amount of Ids
        if (LogIds.Ids.Count() < 20)
        {
            results = results.Where(x => LogIds.Ids.Contains(x.Id));
        }
        else
        {
            // initializing temporary table
            idsTable = await db.CreateTampTableAsync(LogIds.Ids.Select(id => new IdsTable { Id = id }, tableName: "temporaryIds"));
            
            // filter via join
            results =
                from t in idsTable
                join r in results on t.Id equals r.Id
                select r;
        }

        // selecting last log
        results = 
            from r in results
            select new 
            {
                r,
                rn = Sql.Ext.RowNumber().Over()
                    .PartitionBy(r.ContextId)
                    .OrderByDesc(r.CreationDate) 
                    .ToValue()
            } into s
            where s.rn == 1
            select s.r;

        var transformed = await results
            .Select(MapEntityToLogEvent)
            .ToListAsyncLinqToDB(); // we have to use our extension because of name collision with EF Core extensions

    }
    finally
    {
        // dropping temporaty table if it was used
        idsTable?.Dispose();
    }

    return Ok(transformed);
}

Warning

Also note that logs count will grow and you have to limit result set by date and probably count of retrieved records.