SQL Query continues to work for a very long time if a search query is not found

Question

My Azure hosted an ASP.NET Core site, I have a user table, and I searched 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 into an 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 a search query is available in the database, the result is obtained in less than a second. However, if it is not found, the request takes a very long time (I saw it, reaching 48 seconds).

This greatly affects performance when publishing this feature on the Internet.

Can you suggest a way to solve this problem?

thanks

Update: this problem continues here: Empty username when showing sys.processes

Answer 1

You can already simplify your request :):

 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 issue, try creating a stored procedure with your SQL and use it with the Framework entity.

Answer 2

SQL Server should use the scan to find rows matching the .Contains clause. There is no such thing.

However, if we reduce the amount of data that the SQL server scans, we will speed up the query.

Filtered Index Coverage

The index "covers" if it contains all the data that must be returned in the request.

 CREATE INDEX IX_User_Name_filtered ON USER ([Verified], [Name]) INCLUDE ( [PhotoURL], [Id], [Subdomain], [Deleted], [AppearInSearch] ) WHERE [AppearInSearch]=1 AND [Deleted]=0 

This index is probably significantly smaller than the original table, so even if scanning is required, it will be faster.

Depending on the generated plan, this indicator may be the best choice. it does not include additional columns and will be smaller. Testing will be required to determine the best choice.

 CREATE INDEX IX_User_Name_filtered ON USER ([Verified], [Name]) WHERE [AppearInSearch]=1 AND [Deleted]=0