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.

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

Performance issue when querying varbinary(MAX), varchar(MAX), nvarchar(MAX) or XML with Async #88

Closed
Korayem opened this issue Oct 18, 2016 · 13 comments

Comments

@Korayem
Copy link

@Korayem Korayem commented Oct 18, 2016

I am echoing this old issue: https://entityframework.codeplex.com/workitem/2686

There's a huge performance issue when querying a table containing varbinary(MAX) columns with .ToListAsync(). The problem is EF, despite the presence of varbinary(max) column, uses CommandBehavior.Default with ExecuteReaderAsync(). (instead of CommandBehavior.SequentialAccess)

Ado.Net team warns about it. I've made some investigations about this, and I notices a factor 8 between ToList() and ToListAsync() performances.

@Korayem Korayem changed the title Performance issue when querying varbinary(MAX) with Async Performance issue when querying varbinary(MAX), varchar(MAX), nvarchar(MAX) or XML with Async Oct 18, 2016
@Korayem
Copy link
Author

@Korayem Korayem commented Oct 18, 2016

@divega
Copy link

@divega divega commented Oct 18, 2016

@Korayem thanks for reaching out. Not saying there isn't anything anything else we could do to improve the performance, but we are following the recommendations in https://dpaoliello.wordpress.com/2014/04/22/improving-async-performance-for-sqldatareader/.

What version of EF Core are you using. AFAIR we may have made improvements in async performance after 1.0.0. Also have you tried following the recommendations in that article in your model's design?

@Korayem
Copy link
Author

@Korayem Korayem commented Oct 18, 2016

Yeah I am still working on it but was wondering whether this is possible
out of the box. I am on EF 6.1.3

On Tue, Oct 18, 2016 at 1:17 PM, Diego Vega notifications@github.com
wrote:

@Korayem https://github.com/Korayem thanks for reaching out. Not saying
there isn't anything anything we could do to improve the performance, but
we are following the recommendations in https://dpaoliello.wordpress.
com/2014/04/22/improving-async-performance-for-sqldatareader/.

What version of EF Core are you using. AFAIR we may have made improvements
in async performance after 1.0.0. Also have you tried following the
recommendations in that article in your model design?


You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
#88 (comment),
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAMGvEj0SyFEfUmuu5Zzrt663tFDuYYSks5q1JypgaJpZM4KZigs
.

@divega
Copy link

@divega divega commented Oct 18, 2016

What version of EF Core are you using. AFAIR we may have made improvements in async performance after 1.0.0.

When I first read the issue I was under the wrong impression that you were talking about EF Core, not EF6. This sentence is obviously not applicable. Still, we may have made improvements in EF6 releases and especially in .NET Framework releases.

It would be useful if you could provide a repro and information about the specific versions of EF6 and .NET Framework you are using.

@rowanmiller
Copy link
Contributor

@rowanmiller rowanmiller commented Oct 25, 2016

We've discussed at length and concluded that EF is following the recommendations from the post. The biggest takeaway is that app developers should consider avoiding certain data types when using async. From the post...

Avoid using NTEXT, TEXT, IMAGE, TVP, UDT, XML, [N]VARCHAR(MAX) and VARBINARY(MAX) – the maximum data size for these types is so large that it is very unusual (or even impossible) that they would happen to be able to fit within a single packet.

@alexzaytsev-newsroomly
Copy link

@alexzaytsev-newsroomly alexzaytsev-newsroomly commented Jun 18, 2018

@rowanmiller is this still the case with EF core 2.1?

@ajcvickers
Copy link
Member

@ajcvickers ajcvickers commented Jun 18, 2018

@alexzaytsev-newsroomly I don't believe anything in EF Core has changed around this.

@jwbats
Copy link

@jwbats jwbats commented Nov 19, 2020

We've discussed at length and concluded that EF is following the recommendations from the post. The biggest takeaway is that app developers should consider avoiding certain data types when using async. From the post...

@rowanmiller, I would like to understand this issue better. What's the source that you pulled the quote from?

@ErikEJ
Copy link
Contributor

@ErikEJ ErikEJ commented Nov 19, 2020

@Wraith2
Copy link

@Wraith2 Wraith2 commented Nov 19, 2020

@jwbats the quote is from the blog post linked above https://dpaoliello.wordpress.com/2014/04/22/improving-async-performance-for-sqldatareader/

Those recommendations echo my experience as well. Things have improved slightly with Microsoft.Data.SqlClient but there's only so much that can be done with the existing design. Changing how async is implemented in the library is a very difficult task.

@roji
Copy link
Member

@roji roji commented Nov 20, 2020

If I'm understanding this correctly, this issue looks like a dup of dotnet/SqlClient#593, so referring to a SqlClient issue with reading large binary/text data asynchronously.

@ErikEJ
Copy link
Contributor

@ErikEJ ErikEJ commented Nov 20, 2020

@roji yes, just to make aware that this is an age old issue.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
9 participants
You can’t perform that action at this time.