Entity Framework async operation takes ten times as long to complete

async-await asynchronous c# entity-framework

Question

I've been experimenting with altering an MVC site that uses Entity Framework 6 to manage the database such that everything runs as async controllers and calls to the database are run as their equivalent async calls (for example, ToListAsync() instead of ToList()).

Simply switching my queries to async has made them exceedingly slow, which is the issue I'm encountering.

The following code is turned into a reasonably straightforward database join that gets a set of "Album" objects from my data context:

// Get the albums
var albums = await this.context.Albums
    .Where(x => x.Artist.ID == artist.ID)
    .ToListAsync();

The generated SQL is shown below:

exec sp_executesql N'SELECT 
[Extent1].[ID] AS [ID], 
[Extent1].[URL] AS [URL], 
[Extent1].[ASIN] AS [ASIN], 
[Extent1].[Title] AS [Title], 
[Extent1].[ReleaseDate] AS [ReleaseDate], 
[Extent1].[AccurateDay] AS [AccurateDay], 
[Extent1].[AccurateMonth] AS [AccurateMonth], 
[Extent1].[Type] AS [Type], 
[Extent1].[Tracks] AS [Tracks], 
[Extent1].[MainCredits] AS [MainCredits], 
[Extent1].[SupportingCredits] AS [SupportingCredits], 
[Extent1].[Description] AS [Description], 
[Extent1].[Image] AS [Image], 
[Extent1].[HasImage] AS [HasImage], 
[Extent1].[Created] AS [Created], 
[Extent1].[Artist_ID] AS [Artist_ID]
FROM [dbo].[Albums] AS [Extent1]
WHERE [Extent1].[Artist_ID] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=134

It's not a particularly difficult query, but the SQL server is taking almost 6 seconds to execute it. It requires 5742 milliseconds to finish, according to SQL Server Profiler.

Suppose I modify my code to:

// Get the albums
var albums = this.context.Albums
    .Where(x => x.Artist.ID == artist.ID)
    .ToList();

Then the exact same SQL is generated, but according to SQL Server Profiler, this executes in just 474 milliseconds.

The "Albums" table in the database contains about 3500 items, which isn't a lot, plus it has an index on the "Artist ID" column, so it should be rather quick.

Although I am aware that async has overheads, ten times slower performance sounds excessive to me. Where am I making this mistake?

1
137
2/16/2015 3:15:19 PM

Accepted Answer

ZZZ_tmp
280
2/23/2020 4:16:48 AM

Popular Answer

ZZZ_tmp


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow