Question 412127 in Azure Question posted in
The official documentation to get you started can be found here.

Azure – 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.

Another question that arises is whether my Azure SQL server plan is enough. I use the Standard tier with 10 DTUs. Only around 275 MB of DB space is used.

  • 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.
Tags:

2

Answers


  1. 0

    This is sort of a guess, but I think you can try to rewrite it like this

    .Where(p => searchedTerm == "" || EF.Functions.Like(something, $"%{searchedTerm}%"))

    So no SQL is generated if searchterm is "", and else you have the like instead of the charindex.

    Login or Signup to reply.
  2. 0

    As mentioned doing a "like" forces the database to scan, also the if statement complicates the query plan solution further

    Can you rather select the data and do the contains on the app side?

    var linkageData = query
    .SelectMany(p => p.AddressBookLinkage)
    .AsNoTracking()
    .Select(linkage => new 
    {
        MainContactId = linkage.MainContactId,
        Name = linkage.MainContactId == null ? linkage.MainSubject.Name : linkage.MainContact.Name
    })
    .ToList();

var result = linkageData
    .Where(linkage => linkage.Name.Contains(searchedTerm))
    .ToList();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search