Ef6: Performance issue when querying varbinary(MAX), varchar(MAX), nvarchar(MAX) or XML with Async

[Korayem]

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]

This article also talks about same issue: https://dpaoliello.wordpress.com/2014/04/22/improving-async-performance-for-sqldatareader/

[Korayem]

varchar(max) vs varchar(255): https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4d9c6504-496e-45ba-a7a3-ed5bed731fcc/varcharmax-vs-varchar255?forum=sqlgetstarted

[divega]

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

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 [email protected]
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
https://github.com/aspnet/EntityFramework6/issues/88#issuecomment-254466296,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAMGvEj0SyFEfUmuu5Zzrt663tFDuYYSks5q1JypgaJpZM4KZigs
.

[divega]

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]

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]

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

[ajcvickers]

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

[jwbats]

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]

@roji @wraith2

[Wraith2]

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

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

[ErikEJ]

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