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?