如果找不到搜索词,SQL查询将继续运行很长时间

在我的Azure托管的ASP.NET核心站点中,我有一个用户表,我实现了如下搜索：

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语句：

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秒).

当我们将此功能发布到互联网时,这会极大地影响性能.

你能否提出解决这个问题的方法？

谢谢

更新：此问题在此处继续：Empty Login Name When Showing sys.processes

解决方法

您已经可以像这样简化查询;)：

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,AppearInSearch = user.AppearInSearch
                   }
             ).Skip(start).Take(recordsInPage);
 
return await inner.ToListAsync();

如果遇到性能问题,请尝试使用sql创建存储过程并将其与实体框架一起使用.

如果找不到搜索词,SQL查询将继续运行很长时间

解决方法

猜你在找的MsSQL相关文章