Query Timeout in EF Core but runs fast in SSMS-CodePudding

I have a use case in which I am passing a query to a function and then doing some calculations. The query is formed based on what filters I pass. Below is the sample code

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
}

The problem I am facing is that there is a filter named 'filename' and when I pass that filter the underlying query that gets generated is given below

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

Now this query runs very fast on the SQL Server Management Studio but when I debug my C# code and as soon as the execution hits await query.CountAsync() its starts loading on the UI and then after a certain period it times out. Can someone help me debug this? When I send other filters like date it works fine but when I send name its then when its starts taking time but only on the C# side as I have checked that the generated query runs pretty fast.

CodePudding user response：

Due to you having lots of rows in your Document table, you are doing a non-sargable scan of the whole table! which is not ideal. Ideally you want to make it sargable so remove the leading or trailing % signs.

e.g. if you remove the leading % then it will be able to use an index on the FirstName column (as long as you create one). It can then seek to matches and just read that data and not the whole table.

You can try something like this in your linq code

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

If you want more control consider a stored procedure, but if this does the job then all good :)

If you just created an index on the FileName and kept the %...% syntax, It may use the index as mentioned by others above, it would still be a full scan of that index. The index will be smaller than the clustered index table, hence why SQL Server would use it, because it will do less logical page reads.