EF Core SQL Server Efficient Way To Handle Many WHERE CONTAINS-CodePudding

I have a list of varying amount of keywords/phrases which I use as follows to create a WHERE CONTAINS query. I'm trying to exclude all articles that contain one of the keywords/phrases.

        if (itemQuery?.Keywords != null)
        {
            foreach (var keyword in itemQuery.Keywords)
            {
                var upperKeyword = keyword.Text.ToUpper();
                queryable = queryable.Where(x =>
                    !x.TitleUpper.Contains(upperKeyword));
            }
        }

However, the list itemQuery?.Keywords now has 45 keywords in it (And potentially more) and is CONSIDERABLY slowing down the EF Core query. It's taking 5 to 10 seconds to return the data. If I remove this part of the query, it's back to ms to return.

Any tips or ideas on how to make improve the speed/efficiency of this query.

Update 29/01

I updated the field TitleUpper to full text in the hope that this would improve the speed. Below is my implementation of what I tried.

    var keywords = string.Join(" OR ", itemQuery.Keywords.Select(x => $"\"{x}\""));
    queryable = queryable.Where(f => !EF.Functions.Contains(f.TitleUpper, keywords));

The string passed would be formatted like so

" phrase here" OR "word" OR "something"

etc.. with all 40 phrases/words. Unfortunately, when I run this, it seems to take longer than the original query! Have a implemented this incorrectly?

CodePudding user response：

This might get you a better SQL query (I'm pretty sure the logic matches what you're doing):

if (itemQuery?.Keywords != null)
{
    queryable = queryable.Where(x => itemQuery.Keywords.Any(k => !x.TitleUpper.Contains(k.Text.ToUpper())));
}

But maybe not. The problem is that Contains("something") translates to LIKE '%something%' in SQL. That type of query is inefficient because an index on that column (if there is one) can't be used. It has to read every single row in your table to find matches. So the larger your table, the longer it will take.

If you are able to change that query to StartsWith() instead, then, if you have an index on that column, your query would be so much faster.

If you can't change the query, then you could look at other Where conditions that you're using. Try to narrow down the result set with other, indexed columns to limit the number of rows that the Contains needs to be done on.

You can also look at implementing Full-Text Search on that column in SQL, which will increase performance on that type of search. I don't know if Entity Framework will automatically use full-text search, but I know there is support for it.

CodePudding user response：

To anyone else having similar issues. Turns out, that EF Core really doesn't like NVARCHAR(MAX) fields! I reduced a field to NVARCHAR(500) and it made a huge difference in the speed of the query.