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 · 8 comments

Comments

Projects
None yet
5 participants
@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 from Performance issue when querying varbinary(MAX) with Async to Performance issue when querying varbinary(MAX), varchar(MAX), nvarchar(MAX) or XML with Async Oct 18, 2016

@Korayem

This comment has been minimized.

Show comment
Hide comment

Korayem commented Oct 18, 2016

@divega

This comment has been minimized.

Show comment
Hide comment
@divega

divega Oct 18, 2016

Member

@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?

Member

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

This comment has been minimized.

Show comment
Hide comment
@Korayem

Korayem 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
.

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

This comment has been minimized.

Show comment
Hide comment
@divega

divega Oct 18, 2016

Member

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.

Member

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

This comment has been minimized.

Show comment
Hide comment
@rowanmiller

rowanmiller Oct 25, 2016

Member

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.

Member

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

This comment has been minimized.

Show comment
Hide comment
@alexzaytsev-newsroomly

alexzaytsev-newsroomly Jun 18, 2018

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

alexzaytsev-newsroomly commented Jun 18, 2018

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

@ajcvickers

This comment has been minimized.

Show comment
Hide comment
@ajcvickers

ajcvickers Jun 18, 2018

Member

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

Member

ajcvickers commented Jun 18, 2018

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment