实体框架异步操作需要十次只要完成 [英] Entity Framework async operation takes ten times as long to complete
问题描述
我已经得到了一个使用实体框架6来处理数据库的MVC网站，我已经改变它，使一切运行异步控制器和调用到数据库中运行他们的同行异步（如在尝试。 ToListAsync（）代替了ToList（））
I’ve got an MVC site that’s using Entity Framework 6 to handle the database, and I’ve been experimenting with changing it so that everything runs as async controllers and calls to the database are ran as their async counterparts (eg. ToListAsync() instead of ToList())
我遇到的问题是，简单地改变我的查询异步造成它们是难以置信的慢。
The problem I’m having is that simply changing my queries to async has caused them to be incredibly slow.
以下code会从我的数据上下文相册对象的集合，并转换为一个相当简单的数据库连接：
The following code gets a collection of "Album" objects from my data context and is translated to a fairly simple database join:
// Get the albums
var albums = await this.context.Albums
.Where(x => x.Artist.ID == artist.ID)
.ToListAsync();
下面是所创建的SQL：
Here’s the SQL that’s created:
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
由于事情，它不是一个大规模复杂的查询，但它几乎回吐6秒SQL服务器来运行它。 SQL Server事件探查报告为服用5742ms完成。
As things go, it’s not a massively complicated query, but it’s taking almost 6 seconds for SQL server to run it. SQL Server Profiler reports it as taking 5742ms to complete.
如果我改变我的code为：
If I change my code to:
// Get the albums
var albums = this.context.Albums
.Where(x => x.Artist.ID == artist.ID)
.ToList();
然后完全相同的SQL生成，但这种运行在短短的474ms根据SQL Server事件探查。
Then the exact same SQL is generated, yet this runs in just 474ms according to SQL Server Profiler.
该数据库拥有约3500行中的相册表，这是不是真的很多，并且对Artist_ID列的索引，所以它应该是pretty快。
The database has around 3500 rows in the "Albums" table, which isn’t really very many, and has an index on the "Artist_ID" column, so it should be pretty fast.
我知道异步有开销，但使事情更慢十倍似乎有点陡峭给我！我在哪里去错在这里？
I know that async has overheads, but making things go ten times slower seems a bit steep to me! Where am I going wrong here?
推荐答案
我发现这个问题很有趣，尤其是因为我使用异步与Ado.Net无处不在， EF 6.我希望有人给个说法了这个问题，但它并没有发生。所以，我想重现在我身边这个问题。我希望你们中的一些会发现这个有趣。
I found this question very interesting, especially since I'm using async everywhere with Ado.Net and EF 6. I was hoping someone to give an explanation for this question, but it doesn't happened. So I tried to reproduce this problem on my side. I hope some of you will find this interesting.
第一个好消息：我转载它:)而差异是巨大的。随着因子8 ...
First good news : I reproduced it :) And the difference is enormous. With a factor 8 ...
首先，我怀疑事情处理<一个href=\"https://msdn.microsoft.com/en-us/library/system.data.commandbehavior%28v=vs.110%29.aspx\"><$c$c>CommandBehavior,因为<一个href=\"http://blogs.msdn.com/b/adonet/archive/2012/04/20/using-sqldatareader-s-new-async-methods-in-net-4-5-beta.aspx\">I阅读关于一篇有趣的文章异步与废话不多说，说这句话的：
First I was suspecting something dealing with CommandBehavior, since I read an interesting article about async with Ado, saying this :
由于非顺序访问模式，具有存储整个行的数据，它可以，如果你从服务器（如VARBINARY（MAX），VARCHAR（MAX），nvarchar的读取大柱造成的问题（MAX ）或XML）。
"Since non-sequential access mode has to store the data for the entire row, it can cause issues if you are reading a large column from the server (such as varbinary(MAX), varchar(MAX), nvarchar(MAX) or XML)."
我怀疑了ToList（）呼吁为 CommandBehavior.SequentialAccess 和异步的人是 CommandBehavior.Default （非连续的，这可能会导致问题）。于是我下载了EF6的来源，并把断点到处（其中的CommandBehavior 其中，使用，当然）。
I was suspecting ToList() calls to be CommandBehavior.SequentialAccess and async ones to be CommandBehavior.Default (non-sequential, which can cause issues). So I downloaded EF6's sources, and put breakpoints everywhere (where CommandBehavior where used, of course).
结果：什么。所有的呼叫与 CommandBehavior.Default 发....于是，我就踏进EF code明白发生了什么... ..和.. ooouch我从来没有看到这样的委托code，一切似乎都懒执行...
Result : nothing. All the calls are made with CommandBehavior.Default .... So I tried to step into EF code to understand what happens... and.. ooouch... I never see such a delegating code, everything seems lazy executed...
于是，我就做了一些分析，以了解发生了什么......
So I tried to do some profiling to understand what happens...
和我想我有什么......
And I think I have something...
下面是创建我基准，3500线在它的内部，和256 KB随机数据中的每个 VARBINARY（MAX）表中的模型。 （EF 6.1 - codeFirst - codePLEX ）：
Here's the model to create the table I benchmarked, with 3500 lines inside of it, and 256 Kb random data in each varbinary(MAX). (EF 6.1 - CodeFirst - CodePlex) :
public class TestContext : DbContext
{
public TestContext()
: base(@"Server=(localdb)\\v11.0;Integrated Security=true;Initial Catalog=BENCH") // Local instance
{
}
public DbSet<TestItem> Items { get; set; }
}
public class TestItem
{
public int ID { get; set; }
public string Name { get; set; }
public byte[] BinaryData { get; set; }
}
和这里的code我用来创建测试数据，和基准EF
And here's the code I used to create the test data, and benchmark EF.
using (TestContext db = new TestContext())
{
if (!db.Items.Any())
{
foreach (int i in Enumerable.Range(0, 3500)) // Fill 3500 lines
{
byte[] dummyData = new byte[1 << 18]; // with 256 Kbyte
new Random().NextBytes(dummyData);
db.Items.Add(new TestItem() { Name = i.ToString(), BinaryData = dummyData });
}
await db.SaveChangesAsync();
}
}
using (TestContext db = new TestContext()) // EF Warm Up
{
var warmItUp = db.Items.FirstOrDefault();
warmItUp = await db.Items.FirstOrDefaultAsync();
}
Stopwatch watch = new Stopwatch();
using (TestContext db = new TestContext())
{
watch.Start();
var testRegular = db.Items.ToList();
watch.Stop();
Console.WriteLine("non async : " + watch.ElapsedMilliseconds);
}
using (TestContext db = new TestContext())
{
watch.Restart();
var testAsync = await db.Items.ToListAsync();
watch.Stop();
Console.WriteLine("async : " + watch.ElapsedMilliseconds);
}
using (var connection = new SqlConnection(CS))
{
await connection.OpenAsync();
using (var cmd = new SqlCommand("SELECT ID, Name, BinaryData FROM dbo.TestItems", connection))
{
watch.Restart();
List<TestItem> itemsWithAdo = new List<TestItem>();
var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SequentialAccess);
while (await reader.ReadAsync())
{
var item = new TestItem();
item.ID = (int)reader[0];
item.Name = (String)reader[1];
item.BinaryData = (byte[])reader[2];
itemsWithAdo.Add(item);
}
watch.Stop();
Console.WriteLine("ExecuteReaderAsync SequentialAccess : " + watch.ElapsedMilliseconds);
}
}
using (var connection = new SqlConnection(CS))
{
await connection.OpenAsync();
using (var cmd = new SqlCommand("SELECT ID, Name, BinaryData FROM dbo.TestItems", connection))
{
watch.Restart();
List<TestItem> itemsWithAdo = new List<TestItem>();
var reader = await cmd.ExecuteReaderAsync(CommandBehavior.Default);
while (await reader.ReadAsync())
{
var item = new TestItem();
item.ID = (int)reader[0];
item.Name = (String)reader[1];
item.BinaryData = (byte[])reader[2];
itemsWithAdo.Add(item);
}
watch.Stop();
Console.WriteLine("ExecuteReaderAsync Default : " + watch.ElapsedMilliseconds);
}
}
using (var connection = new SqlConnection(CS))
{
await connection.OpenAsync();
using (var cmd = new SqlCommand("SELECT ID, Name, BinaryData FROM dbo.TestItems", connection))
{
watch.Restart();
List<TestItem> itemsWithAdo = new List<TestItem>();
var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
while (reader.Read())
{
var item = new TestItem();
item.ID = (int)reader[0];
item.Name = (String)reader[1];
item.BinaryData = (byte[])reader[2];
itemsWithAdo.Add(item);
}
watch.Stop();
Console.WriteLine("ExecuteReader SequentialAccess : " + watch.ElapsedMilliseconds);
}
}
using (var connection = new SqlConnection(CS))
{
await connection.OpenAsync();
using (var cmd = new SqlCommand("SELECT ID, Name, BinaryData FROM dbo.TestItems", connection))
{
watch.Restart();
List<TestItem> itemsWithAdo = new List<TestItem>();
var reader = cmd.ExecuteReader(CommandBehavior.Default);
while (reader.Read())
{
var item = new TestItem();
item.ID = (int)reader[0];
item.Name = (String)reader[1];
item.BinaryData = (byte[])reader[2];
itemsWithAdo.Add(item);
}
watch.Stop();
Console.WriteLine("ExecuteReader Default : " + watch.ElapsedMilliseconds);
}
}
对于经常EF调用（ .ToList（））的分析似乎正常，并且是易于阅读：
For the regular EF call (.ToList()), the profiling seems "normal" and is easy to read :
![](\"http://i.stack.imgur.com/05Bo9.jpg\")
在这里我们发现8.4秒我们都用秒表（剖析缓慢起伏的perfs）。我们还发现HitCount = 3500沿呼叫路径，女巫与线3500在测试中保持一致。在TDS解析器侧，做起事情来，因为我们读 TryReadByteArray 118 353电话（）方法，它是缓冲环路发生变差。 （平均33.8呼吁每个字节[] 256KB的）
Here we find the 8.4 seconds we have with the Stopwatch (profiling slow downs the perfs). We also find HitCount = 3500 along the call path, witch is consistent with the 3500 lines in the test. On the TDS parser side, things start to became worse since we read 118 353 calls on TryReadByteArray() method, which is were the buffering loop occurs. (an average 33.8 calls for each byte[] of 256kb)
对于异步情况下，它的的确确是不同的....首先， .ToListAsync（）通话定于线程池，然后等待。没有什么神奇的在这里。但是，现在，这里的一对线程池的异步地狱：
For the async case, it's really really different.... First, the .ToListAsync() call is scheduled on the ThreadPool, and then awaited. Nothing amazing here. But, now, here's the async hell on the ThreadPool :
![](\"http://i.stack.imgur.com/Tik4x.jpg\")
首先，在第一种情况下，我们都具有沿完整的呼叫路径只是3500命中次数，我们这里有118 371.此外，你必须想象所有的同步调用我没有穿上screenshoot ...
First, in the first case we were having just 3500 hit counts along the full call path, here we have 118 371. Moreover, you have to imagine all the synchronization calls I didn't put on the screenshoot...
二，在第一种情况下，我们有只是118 353呼叫转移到 TryReadByteArray（）方法，在这里我们有2 050 210话费！它更17倍...（与大阵1Mb的测试，它的160倍以上）
Second, in the first case, we were having "just 118 353" calls to the TryReadByteArray() method, here we have 2 050 210 calls ! It's 17 times more... (on a test with large 1Mb array, it's 160 times more)
此外还有：
- 120 000
工作实例创建 - 727 519
互锁通话 - 290 569
显示器通话 - 98 283
的ExecutionContext情况下，具有264 481捕获 - 208 733
自旋锁要求
- 120 000
Taskinstances created - 727 519
Interlockedcalls - 290 569
Monitorcalls - 98 283
ExecutionContextinstances, with 264 481 Captures - 208 733
SpinLockcalls
我的猜测是缓冲是在异步的方式（而不是一个很好的）制成，具有并行任务试图从TDS读取数据。太多的任务创建只是解析二进制数据。
My guess is the buffering is made in an async way (and not a good one), with parallel Tasks trying to read data from the TDS. Too many Task are created just to parse the binary data.
作为preliminary总之，我们可以说异步是伟大的，EF6是伟大的，但EF6在它异步的惯例是当前实现增加了一个大的开销，在性能方面，该线程的一面，和CPU侧（在了ToList（）情况下，12％的CPU使用率，并在 ToListAsync 情况下，20％的不再是8至10倍工作......我的老酷睿i7 920）运行它。
As a preliminary conclusion, we can say Async is great, EF6 is great, but EF6's usages of async in it's current implementation adds a major overhead, on the performance side, the Threading side, and the CPU side (12% CPU usage in the ToList() case and 20% in the ToListAsync case for a 8 to 10 times longer work... I run it on an old i7 920).
虽然所作所为一些测试，我在想<一个href=\"http://blogs.msdn.com/b/adonet/archive/2012/04/20/using-sqldatareader-s-new-async-methods-in-net-4-5-beta.aspx\">this本文试，我注意到一些我错过了：
While doings some tests, I was thinking about this article again and I notice something I miss :
对于.NET 4.5的新异步方法，他们的行为是完全一样的同步方法，但有一个明显的例外：在ReadAsync非连续模式
"For the new asynchronous methods in .Net 4.5, their behavior is exactly the same as with the synchronous methods, except for one notable exception: ReadAsync in non-sequential mode."
什么？！
所以，我致以基准，以包含常规/异步调用Ado.Net，并与 CommandBehavior.SequentialAccess / CommandBehavior.Default ，这里是一个巨大的惊喜！
So I extend my benchmarks to include Ado.Net in regular / async call, and with CommandBehavior.SequentialAccess / CommandBehavior.Default, and here's a big surprise ! :
我们有Ado.Net完全相同的行为！捂脸......
We have the exact same behavior with Ado.Net !!! Facepalm...
我的最终结论是：：有一个在EF 6实现中的错误。它应该切换的CommandBehavior 到 SequentialAccess 当一个异步调用通过包含一个表做了一个二进制（最大）列。创造了太多的任务，减慢这一过程的问题，是关于Ado.Net一面。该EF问题是，它不使用Ado.Net因为它应该。
My definitive conclusion is : there's a bug in EF 6 implementation. It should toggle the CommandBehavior to SequentialAccess when an async call is made over a table containing a binary(max) column. The problem of creating too many Task, slowing down the process, is on the Ado.Net side. The EF problem is it don't uses Ado.Net as it should.
现在你知道，而不是使用EF6异步方法，你最好要调用EF在常规的非异步的方式，然后用 TaskCompletionSource＆LT; T＆GT; 返回在异步方式的结果。
Now you know instead of using the EF6 async methods, you would better have to call EF in a regular non-async way, and then use a TaskCompletionSource<T> to return the result in an async way.
注1：我编辑，因为一个可耻的错误，我的帖子....我已经做了我的第一个测试在网络上，而不是在本地和有限的带宽已经扭曲的结果。以下是更新的结果。
Note 1 : I edited my post because of a shameful error.... I've done my first test over the network, not locally, and the limited bandwidth have distorted the results. Here are the updated results.
注2：我没有我的测试扩展到其他用途的情况下（例如：为nvarchar（最大）用大量的数据），但也有机会在同一行为发生。
Note 2 : I didn't extends my test to other uses cases (ex : nvarchar(max) with a lot of data), but there are chances the same behavior happens.
注3：平常的东西对了ToList（）的情况下，是12％的CPU（我的CPU = 1的逻辑核心的1/8）。一些不寻常的是 ToListAsync（）情况下，最大20％，好像调度程序不能使用所有的战靴。这可能是由于创建了太多的任务，或者在TDS解析器的一个瓶颈，我不知道......
Note 3 : Something usual for the ToList() case, is the 12% CPU (1/8 of my CPU = 1 logical core). Something unusual is the maximum 20% for the ToListAsync() case, as if the Scheduler could not use all the Treads. It's probably due to the too many Task created, or maybe a bottleneck in TDS parser, I don't know...
这篇关于实体框架异步操作需要十次只要完成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
