Query execution is very slow due to DataReader disposing taking too long

[vladislav-naydenov]

I have a simple query which runs very slow:

var photoSessions = await this.AllAsNoTracking()
                .Select(p => new PhotoSessionResponseModel
                {
                    Id = p.Id,
                    Price = p.Price,
                    Frames = p.Frames,
                    BackgroundImageUri = p.BackgroundImageUri,
                    Details = p.PhotoSessionDetails.Select(pd => new PhotoSessionDetailsResponseModel
                    {
                        Id = pd.Id,
                        Type = pd.Type,
                        LanguageCode = pd.LanguageCode,
                        Description = HtmlUtils.HtmlDecode(pd.Description)
                    }).ToList()
                })
                .ToListAsync();

I have enabled logging and found out that the db query gets executed very fast (10ms) but the slowness comes from disposing the DataReader which takes more than 10 seconds sometimes.

[smitpatel]

10ms is time to execute DbCommand and generate DbDataReader. Data reader gets disposed only after all results have been read, which can take time based on how much data is in result set. Is this happening on empty table?

[vladislav-naydenov]

It's happening on a table with 1 record and the joined table has 2 records.

[smitpatel]

Can you share a runnable repro code (with data) which demonstrate what you are seeing?

[vladislav-naydenov]

Hi @smitpatel,

I've created a project for reproducing the issue. This is the repo: https://github.com/vladislav-naydenov/BlazorEfCoreRepro. The file https://github.com/vladislav-naydenov/BlazorEfCoreRepro/blob/master/Server/Properties/DBPopulate.sql contains the queries for populating the db with the same data I'm using.

Start the project and navigate to /PhotoSessions.

[ajcvickers]

@vladislav-naydenov Looks like you are running into this: dotnet/SqlClient#593 due to loading a very long (> 1MB) string. Changing from ToListAsync to ToList drastically improves performance on my machine.

/cc @roji

[roji]

@ajcvickers yep, looks like another case of this.

[roji]

Duplicate of dotnet/SqlClient#593

[vladislav-naydenov]

@ajcvickers, @roji

Thanks for the clarification. Will switch to using ToList until the issue is resolved