Optimizing LINQ performance for large database tables

Data and Databases
1

I’m working with a big database table and need to speed up my LINQ queries. Right now I’m using the Contains method to search for names and then linking that to another table. Here’s what my code looks like:

var matchingIds = myDbContext.BigTable
    .Where(item => item.Name.ToLower().Contains(searchText.ToLower()))
    .Select(item => item.Id)
    .ToList();

var relatedIds = new List<int>();
foreach (var id in matchingIds)
{
    relatedIds.AddRange(myDbContext.RelatedTable
        .Where(x => x.BigTableId == id)
        .Select(x => x.Id));
}

It works, but it’s pretty slow when dealing with thousands of records. Are there any tricks to make this run faster? Maybe some LINQ magic I’m missing? Thanks for any tips!

2

Have you considered using SQL Server’s full-text search capabilities? It’s significantly faster than LIKE or CONTAINS for text searches in large tables. You’d need to set up a full-text index on the Name column, but it could drastically improve performance.

For the related IDs, a single query with a join would be more efficient:

var relatedIds = myDbContext.BigTable
    .Where(item => EF.Functions.Contains(item.Name, searchText))
    .SelectMany(item => myDbContext.RelatedTable
        .Where(x => x.BigTableId == item.Id)
        .Select(x => x.Id))
    .ToList();

This approach minimizes database round-trips and leverages SQL Server’s optimization capabilities. Remember to use async methods like ToListAsync() for better responsiveness in web applications.

3

have you considered using a join instead of multiple queries? it could really speed things up! something like:

var relatedIds = myDbContext.BigTable
    .Where(item => item.Name.ToLower().Contains(searchText.ToLower()))
    .Join(myDbContext.RelatedTable,
        bigitem => bigitem.Id,
        relatedItem => relatedItem.BigTableId,
        (bigitem, relatedItem) => relatedItem.Id)
    .ToList();

what do you think? might be worth a try!

4

hey, have u tried a fulltext index on the name column? it speeds up contains searches a lot. also, you can combine queries with a join to avoid multiple db calls. using SelectMany for related ids might help performance!