LINQ query is too slow

I am working on a middle size web application that handles projects, invoices etc.
I struggle to optimize my filter query for a list of projects whenever I filter by subject’s substring. It sometimes searches for up to one whole minute – and the DTU in my azure app sky rockets to 100 %.

This is the LINQ Query:

if (!Filter.Client.IsNullOrEmpty())
{
    var searchedTerm = Filter.Client;
    query = query.Where(p =>
                            p.AddressBookLinkage
                                .Any(linkage => (linkage.MainContactId == null
                                ? linkage.MainSubject.Name
                                : linkage.MainContact.Name).Contains(searchedTerm)));
    var sqlQuery = query.ToList();
}

AddressBookLinkage holds the IDs of either the subject or contact ID. Each project contains exactly one of them, the other being null.

This is the generated SQL of the subquery that handles the filter on this column

    SELECT 1
    FROM [AddressBookProjectLinkages] AS [a]
    LEFT JOIN [Subjects] AS [s] ON [a].[MainSubjectId] = [s].[Id]
    LEFT JOIN [Contacts] AS [c] ON [a].[MainContactId] = [c].[Id]
    WHERE ([p].[Id] = [a].[ProjectId]) AND ((@__searchedTerm_2 LIKE N'') OR (CHARINDEX(@__searchedTerm_2, CASE
        WHEN [a].[MainContactId] IS NULL THEN [s].[Name]
        ELSE [c].[Name]
    END) > 0)))
ORDER BY [p].[Id]

I think it is important to mention that for some reason, this filter is running in my local environment much smoother than on the production (with the production data). The web app including the SQL server is running on Azure services. However, I can see that such a significant performance difference only happens on this filter.

• I set up an index on Name of both Subject and Contact, only to realize that the index is probably ignored once I use SQL CHARINDEX or LIKE conditions.
• I implemented Full text search on my database and set the index on those two columns, but then I figured out that FTS is not used for substring search.

You need to sign in to view this answers