DapperLib / DapperLib/Dapper

Long execution/mapping in QueryMultipleAsync with OFFSET / FETCH

Open
#1,628 3 comments 0 reactions 0 assignees View on GitHub

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

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.