sql server - SQL Query continues running for a very long time if search term not found

Question

In my Azure hosted ASP.NET Core site I have a table of users and I implemented search as follows:

    var inner = from user in db.Users
             select  new
             {
                 Name = user.Name,
                 Verified = user.Verified,
                 PhotoURL = user.PhotoURL,
                 UserID = user.Id,
                 Subdomain = user.Subdomain,
                 Deleted=user.Deleted,
                 AppearInSearch = user.AppearInSearch
             };
    return await inner.Where(u=>u.Name.Contains(name)&& !u.Deleted && u.AppearInSearch)
                                    .OrderByDescending(u => u.Verified)
                                    .Skip(page * recordsInPage)
                                    .Take(recordsInPage)
                                    .Select(u => new UserSearchResult()
                                    {
                                        Name = u.Name,
                                        Verified = u.Verified,
                                        PhotoURL = u.PhotoURL,
                                        UserID = u.UserID,
                                        Subdomain = u.Subdomain
                                    }).ToListAsync();

This translates to a SQL statement similar to the following:

SELECT [t].[Name], [t].[Verified],
       [t].[PhotoURL], [t].[Id], 
       [t].[Subdomain], [t].[Deleted], 
       [t].[AppearInSearch]  
FROM (      
        SELECT [user0].[Name], [user0].[Verified], 
               [user0].[PhotoURL], [user0].[Id], 
               [user0].[Subdomain], [user0].[Deleted], 
               [user0].[AppearInSearch]      
        FROM [AspNetUsers] AS [user0]
        WHERE (((CHARINDEX('khaled', [user0].[Name]) > 0) OR ('khaled' = N'')) 
          AND ([user0].[Deleted] = 0)) 
          AND ([user0].[AppearInSearch] = 1)      
        ORDER BY [user0].[Verified] DESC      
        OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY  ) AS [t]

If the search term is available in the database, the result is obtained in less than a second. However, If it's not found the query runs for a very long time (I have seen it once reaching 48 seconds).

This greatly affects performance when we publish this feature to the internet.

Can you kindly suggest a way to solve this issue?

Thank you

Update: this issue is continued here: Empty Login Name When Showing sys.processes

Answer 1

Already you can simplify your query like this ;) :

int start=page * recordsInPage;

var inner = (from user in db.Users
            where user.Name.Contains(name) && !user.Deleted && user.AppearInSearch
            orderby user.Verified descending
            select  new
                   {
                     Name = user.Name,
                     Verified = user.Verified,
                     PhotoURL = user.PhotoURL,
                     UserID = user.Id,
                     Subdomain = user.Subdomain,
                     Deleted=user.Deleted,
                     AppearInSearch = user.AppearInSearch
                   }
             ).Skip(start).Take(recordsInPage);

return await inner.ToListAsync();

If you have a performance problem, try to create a stored procedure with your SQL and use it with entity Framework.