Slow retrieval of data with a filter between two dates

[ermaaga]

Hi,
i'm developing an Audit Service, that retrieve datas from SQL Database between two dates, i don't have problems with a small period (like 10 days) but the response is slow when i use a longer period, (like 30 days), this method response slowly:

He needs more than 1.50 sec for retrive the datas from the query:

public async Task<IQueryable<AuditEntry>> GetAllQueryableAsync(ODataQueryOptions<AuditEntry> options) { var tempVar = dbContext.AuditEntries.GetQueryAsync(_mapper, options, new QuerySettings { ODataSettings = new ODataSettings { HandleNullPropagation = HandleNullPropagationOption.False } }).Result.ToList(); return await dbContext.AuditEntries.GetQueryAsync(_mapper, options, new QuerySettings { ODataSettings = new ODataSettings { HandleNullPropagation = HandleNullPropagationOption.False } }); }

If i run the query on the database, it takes only 0.01 second

DECLARE @__TypedProperty_0 nvarchar(4000)
DECLARE @__TypedProperty_1 datetime
DECLARE @__TypedProperty_2 datetime
DECLARE @__p_3 int
DECLARE @__p_4 int

SET @__TypedProperty_0=N'E6CAAA5B-DC15-47F6-B72F-A7DE42D86ECB'
SET @__TypedProperty_1='2021-10-10 02:00:00'
SET @__TypedProperty_2='2021-11-10 01:00:00'
SET @__p_3=0
SET @__p_4=20

SELECT [t0].[Id], [t0].[Timestamp], [u].[User], [t1].[TenantGroup], [t0].[TenantName], [e].[EntityName], [t0].[EntityCode], [e0].[EntityAction], [e1].[EntityRoot], [o].[OfficeDescription], [t0].[UserMessage], [e2].[ErrorMessage], [t0].[EntityId], [t0].[EntityRootId], [t2].[Ttl]
FROM (
SELECT [a].[Id], [a].[EntityActionId], [a].[EntityCode], [a].[EntityId], [a].[EntityNameId], [a].[EntityRootId], [a].[EntityRootNameId], [a].[ErrorMessageId], [a].[OfficeDescriptionId], [a].[TenantGroupId], [a].[Timestamp], [a].[TtlId], [a].[UserId], [a].[UserMessage], [t].[TenantName]
FROM [Audits_CCI] AS [a]
INNER JOIN [TenantNames] AS [t] ON [a].[TenantNameId] = [t].[Id]
WHERE (([t].[TenantName] = @__TypedProperty_0) AND ([a].[Timestamp] >= @__TypedProperty_1)) AND ([a].[Timestamp] <= @__TypedProperty_2)
ORDER BY [a].[Timestamp]
OFFSET @__p_3 ROWS FETCH NEXT @__p_4 ROWS ONLY
) AS [t0]
INNER JOIN [Users] AS [u] ON [t0].[UserId] = [u].[Id]
INNER JOIN [TenantGroups] AS [t1] ON [t0].[TenantGroupId] = [t1].[Id]
INNER JOIN [EntityNames] AS [e] ON [t0].[EntityNameId] = [e].[Id]
INNER JOIN [EntityActions] AS [e0] ON [t0].[EntityActionId] = [e0].[Id]
INNER JOIN [EntityRootNames] AS [e1] ON [t0].[EntityRootNameId] = [e1].[Id]
INNER JOIN [OfficeDescriptions] AS [o] ON [t0].[OfficeDescriptionId] = [o].[Id]
INNER JOIN [ErrorMessages] AS [e2] ON [t0].[ErrorMessageId] = [e2].[Id]
INNER JOIN [Ttls] AS [t2] ON [t0].[TtlId] = [t2].[Id]
ORDER BY [t0].[Timestamp]

I would like to know, how is it possible that it takes so much time if the query responds in just 0.01 seconds? Why doesn't this happen in a short period of time such as 10 days?
Could it be a mapping problem? but i do the ToList() I always have only 20 records.

Can someone please help me ?

[BlaiseD]

You can narrow it down by trying:

1. ProjectTo without this library.
2. Manual Select (without ProjectTo or this libtary).

If this library is the source then reference the library or SourceLink and go from there.

[ermaaga]

Thank you @BlaiseD i'm using:

AutoMapper;

AutoMapper.AspNet.OData;

The query that returns the records, always has the same time of execution on the database indifferently from the filter on the date (whether it is large or that it is a temporal span is always 0.01 sec).

If the time span inserted as a filter has a 5 -day period, the response time of the "GetQueryAsync" method of the Static Class QueryableExtensions is 1 sec.

If the time span inserted in the filter has a 30 -day period, the response time of the "GetQueryAsync" method of the Static Class QueryableExtensions is 1.55 min

I would like to know, why the method returns such a slow response when I have a larger period.

[BlaiseD]

The GetQueryAsync method just creates an IQueryable here.

To understand the problem I would similarly create one IQueryable just using ProjectTo and another one using a LINQ Select and:

• Compare the performances for any difference.
• Inspect and compare the IQueryable.Expression created by each one.

If the performances are the same then that eliminates GetQueryAsync as the cause. If they are different the IQueryable.Expression should tell you why.

Make sense?