sql - linq教程




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

你可以像这样简化查询;):

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

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

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

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

当我们发布这个功能到互联网时,这大大影响了性能。

你能不能建议一种方法来解决这个问题?

谢谢

更新:这个问题继续在这里: 显示sys.processes空登录名


SQL Server必须使用扫描来查找匹配.Contains子句的行。 这是没有办法的。

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

覆盖过滤的索引

如果索引包含查询中需要返回的所有数据,则索引为“覆盖”。

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

这个索引可能比原来的表要小得多,所以即使需要扫描,速度也会更快。

根据生成的计划,这个指数可能是一个更好的选择。 它不包括额外的列,将会更小。 测试将需要确定最佳选择。

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




asp.net-core