如果未找到搜索词，SQL 查询会继续运行很长时间

2022-01-07 00:00:00 sql sql-server asp.net-core linq linq-to-sql

在我的 Azure 托管 ASP.NET Core 站点中，我有一个用户表，并按如下方式实现了搜索:

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();

这将转换为类似于以下内容的 SQL 语句:

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]

如果搜索词在数据库中可用，则在不到一秒的时间内获得结果.但是，如果查询运行很长时间没有找到(我见过一次达到 48 秒).

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?

谢谢

更新:此问题在此处继续:显示系统时出现空登录名.进程

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

推荐答案

SQL Server 必须使用扫描来查找与 .Contains 子句匹配的行.没有办法解决这个问题.

SQL Server has to use a scan to find rows matching the .Contains clause. There is no way around this.

但是，如果我们减少 SQL Server 必须扫描的数据量，我们将加快查询速度.

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

如果索引包含查询中需要返回的所有数据，则它是覆盖"的.

An index is "covering" if it contains all the data needed to be returned in a query.

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 likely substantially smaller than the original table, so even if a scan is required, it will be quicker.

根据生成的计划，此索引可能是更好的选择.它不包括额外的列并且会更小.需要进行测试以确定最佳选择.

Depending on the plan that is generated, this index may be a better choice. it doesn't include the extra columns and will be smaller still. 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

相关文章