EF Core中的查询超时但在SSMS中运行很快

Question

EF Core中的查询超时但在SSMS中运行很快

c#sql-serverentity-frameworklinqentity-framework-core

5

我有一个使用案例，其中我传递一个查询到一个函数中，然后进行一些计算。查询是根据我传递的过滤器形成的。以下是示例代码：

var totalCount = await query.CountAsync();

var limitExceeded = limit.HasValue && totalCount > limit.Value;
var pagedResults = new List<R>();
// Don't execute the query if the limit has been exceeded
if (!limitExceeded)
{
    //Do some work here
}

我所面临的问题是有一个名为'filename'的过滤器，当我传递该过滤器时，所生成的底层查询如下：

DECLARE @__fileName_1 nvarchar(1024) = N'%cmder.zip%';

SELECT [d].[Id], [d].[CreatedByUserId], [d].[DateCreated], [d].[DateModified], [d].[DatePurged], [d].[Deleted], [d].[DocumentKey], [d].[DocumentStatusId], [d].[DocumentTypeId], [d].[FileDesc], [d].[FileExt], [d].[FileLength], [d].[FileLengthTypeId], [d].[FileName], [d].[FileSize], [d].[FullPath], [d].[Hidden], [d].[ModifiedByUserId], [d].[PurgedByUserId], [d].[RepositoryId], [d].[SHA1], [d].[TransactionId], [d].[UploadedDate], [c].[Id], [c].[CaseId], [c].[DateModified], [c].[Deleted], [c].[DocumentId], [c].[ModifiedByUserId], [c].[PublishToId], [c].[TransactionId], [c0].[Id], [c0].[CaseCoordinatorId], [c0].[CaseName], [c0].[CaseNo], [c0].[CaseTypeId], [c0].[CaseVenueTypeId], [c0].[County], [c0].[Court], [c0].[DateModified], [c0].[DateSettled], [c0].[Deleted], [c0].[Disabled], [c0].[FullCaseName], [c0].[IsComplex], [c0].[IsDepository], [c0].[ModifiedByUserId], [c0].[NameKey], [c0].[Remarks], [c0].[SalesRepId], [c0].[TransactionId], [c0].[TrialDate], [c0].[USStateId], [l].[Name] AS [PublishTo], N'Case' AS [Level]
FROM [Documents].[Document] AS [d]
INNER JOIN [Orders].[CaseDocument] AS [c] ON [d].[Id] = [c].[DocumentId]
INNER JOIN [Orders].[Case] AS [c0] ON [c].[CaseId] = [c0].[Id]
INNER JOIN [Admin].[LookupValue] AS [l] ON [c].[PublishToId] = [l].[Id]
WHERE [d].[FileName] LIKE @__fileName_1
ORDER BY [d].[UploadedDate] DESC

现在这个查询在SQL Server Management Studio中运行非常快，但是当我调试C#代码并且执行到await query.CountAsync()时，它开始在UI上加载，然后在一定时间后超时。有人可以帮助我调试吗？当我发送其他过滤器，如日期时，它正常工作，但当我发送名称时，它开始花费时间，但仅在C#端，因为我已经检查了生成的查询运行得非常快。

- Shubham Tiwari

2

使用 LIKE @__fileName_1 当 @__fileName_1 = N'%cmder.zip%' 时，由于需要进行整个表扫描，因此速度总是很慢的。你应该避免在字符串中搜索字符串，因为无法使用索引。 - Dale K

1

当在SSMS中运行速度很快，但通过ADO.NET很慢时，通常是由于*不同的查询选项（SSMS与ADO.NET有不同的默认值）或参数嗅探引起的问题。无论哪种情况，问题在于当您在SSMS中运行它时，您会得到一个不同的查询计划；请参见https://www.sommarskog.se/query-plan-mysteries.html；添加`OPTION(OPTIMIZE FOR UNKNOWN)`可能会帮助它至少保持一致（尽管这也应该谨慎使用）。 - Marc Gravell

1

@Andrew，有很多行数据，你看到的查询是由EF Core生成的，所以我无法控制它，你知道在LINQ中我可以怎么做吗？ - Shubham Tiwari

2

如果是由LINQ生成的代码，像添加提示这样的操作就会变得非常困难；我的观点是：当LINQ完美运行时，它非常棒；但一旦出现问题，最好停止使用它，取出已生成的查询语句，并使用EF原始API或Dapper来执行该查询，以满足您需要的任何调整。 - Marc Gravell

1

全文搜索有其搜索限制，可能不适用于此情况。那么将“FileName”拆分为两个字段：“路径”和“文件名”如何？ - Svyatoslav Danyliv

显示剩余12条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Andrew · Accepted Answer

由于您的Document表中有很多行，您正在对整个表进行非可搜索扫描！这并不理想。理想情况下，您希望使其可搜索，因此请删除前导或尾随%符号。

例如，如果您删除前导%，则它将能够在FirstName列上使用索引（只要您创建一个）。然后，它可以寻找匹配项并仅读取该数据而不是整个表。

您可以尝试在linq代码中使用以下内容

YourDocuments.Where(x => EF.Functions.Like(x.FileName, $"{yourSearchString}%"))

如果您希望更多的控制，请考虑使用存储过程，但如果这个方法能够胜任工作，那就很好 :)

如果您只是在FileName上创建了一个索引，并保持%...%语法，它可能会像其他人所提到的那样使用该索引，但仍然是对该索引进行全面扫描。索引将比聚集索引表小，因此SQL Server将使用它，因为它将执行较少的逻辑页面读取。