Query execution is very slow due to DataReader disposing taking too long · Issue #23900 · dotnet/efcore · GitHub
Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Closed
vladislav-naydenov opened this issue Jan 15, 2021 · 8 comments
Closed

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

vladislav-naydenov opened this issue Jan 15, 2021 · 8 comments
Labels
closed-external customer-reported

Comments

@vladislav-naydenov
Copy link

vladislav-naydenov commented Jan 15, 2021 •
edited by roji

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.

2021-01-15
@smitpatel
Copy link
Member

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
Copy link
Author

vladislav-naydenov commented Jan 15, 2021 •
edited

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

@smitpatel
Copy link
Member

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

@vladislav-naydenov
Copy link
Author

vladislav-naydenov commented Jan 16, 2021 •
edited

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
Copy link
Member

@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

@anranruye anranruye mentioned this issue Jan 20, 2021
Open
@roji
Copy link
Member

roji commented Jan 20, 2021

@ajcvickers yep, looks like another case of this.

@roji
Copy link
Member

roji commented Jan 20, 2021

Duplicate of dotnet/SqlClient#593

@roji roji marked this as a duplicate of dotnet/SqlClient#593 Jan 20, 2021
@vladislav-naydenov
Copy link
Author

@ajcvickers, @roji

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

@anranruye anranruye mentioned this issue Feb 19, 2021
Closed
@ajcvickers ajcvickers reopened this Oct 16, 2022
@ajcvickers ajcvickers closed this as not planned Won't fix, can't repro, duplicate, stale Oct 16, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
closed-external customer-reported
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@ajcvickers @smitpatel @roji @vladislav-naydenov