Long execution/mapping in QueryMultipleAsync with OFFSET / FETCH
Nobody has claimed this yet.
- Dominant language
- C#
- Stars
- 18.4k
- Forks
- 3.7k
- Avg merge
- 5h 8m
- Merged PRs (30d)
- 1
Description
Hi.
I came across a problem with QueryMultiple execution time. For jumping between pages it's all ok. But when I go to the last page (in this example is 52nd) response is after 20s, sometimes more. Whole query, without pagination, has 600 rows. No delays with Management Studio. Am I doing something wrong?
QUERY
select
*
from
Documents
where Type = @Type
and DocumentDate between @StartDate and @EndDate
order by Id desc
offset @Page rows
fetch next @Limit rows only;
select count(*)
from Documents
where Type = @Type
and DocumentDate between @DateStart and @EndDate
CODE
var documents = new DocumentsResponse();
using (var db = _dbConnectionFactory.CreateCompanyConnection())
{
using (var multi = await db.QueryMultipleAsync(query, new { Page = filter.Limit * filter.Page, filter.Limit, filter.Type, filter.StartDate, filter.EndDate }))
{
documents.Data = multi.Read<Document>();
documents.DataLength = multi.ReadFirst<int>();
}
}
return documents;
FIX - I have to separate the queries and then execute them
var documents = new DocumentsResponse();
using (var db = _dbConnectionFactory.CreateCompanyConnection())
{
documents.Data = await db.QueryAsync<Document>(query1, new { Page = filter.Limit * filter.Page, filter.Limit, filter.Type, filter.StartDate, filter.EndDate }));
documents.DataLength = await db.QueryFirstOrDefaultAsync<int>(query2, new { Page = filter.Limit * filter.Page, filter.Limit, filter.Type, filter.StartDate, filter.EndDate }));
}
return documents;
Environment
MS SQL Server 2019 Developer
Dapper 2.0.35
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start by reproducing the QueryMultipleAsync call from the issue with SQL Server 2019 and compare it with the separate QueryAsync and QueryFirstOrDefaultAsync calls. Inspect the two SQL statements, OFFSET/FETCH parameters, and Dapper 2.0.35 behavior to determine whether the delay is in SQL execution or mapping. Done means identifying a reproducible cause and documenting the responsible layer.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- csharp, sql
- Domain
- backend, database
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 25/100