实体框架和处理查询结果的性能不一致 [英] Entity Framework and inconsistent performance with processing the results of a query
问题描述
只需在我们的网站上设置新的遗物，我们注意到我们的一个webapi电话中有一些奇怪的尖峰。
Just setup New Relic on our site and we noticed some odd spikes in one of our webapi calls.
在这一点上，我将问题归结为一个单一的EF查询，我移出自己的方法，所以我可以使用新的遗物来衡量它..
At this point I have the problem boiled down to a single EF query that I moved out to its own method so I could use New Relic to instrument it..
该方法唯一的做法是
//I changed this from ToListAsync to ToList to see if it would help
var result = Context.Data.Where(x=> x.id = id).ToList();
return result;
就是这样..
Relic并且还注销了所有的SQL查询我相当积极的问题是不是查询缓慢。 New Relic表示，该方法只花了339 ms的时间来平衡数据库，我从EF的跟踪数据中看到类似的数字。但方法的平均执行时间为16秒。
Using New Relic and also logging out all the SQL queries I'm fairly positive the issue isn't with the query being slow. New Relic says the method is only spending an average of 339 ms running against the DB and I see similar numbers from the trace data coming from EF. But the methods average execution time is 16 seconds.
通常这种方法在不到2秒钟内执行，但是每隔一段时间，EF似乎都会进入拉拉地区，需要60或70秒才能完成任务。但是我从来没有看到DB查询本身花费的时间长于500 ms。而且每次运行时查询都是完全相同的数据集。
Normally this method executes in under 2 seconds but every so often EF just seems to go off into lala land and it takes 60 or 70 seconds to finish whatever it's doing. But I've never seen the DB query itself take longer then 500 ms. And the query is retrieving pretty much the exact same dataset every time it runs..
我注意到日志中长时间的等待时间总是在执行查询和关闭之间连接..这里是从日志输出的示例
I noticed in the logs that the long wait time was always between executing the query and closing the connection.. Here's an example output from the logs
- 应用程序：2015-02-17T10：04：11 PID [3556]信息 - - 完成6 ms，结果是：SqlDataReader应用程序：应用程序：
- 2015-02-17T10：04：11 PID [3556]信息应用程序：应用程序：
- 2015-02-17T10：04：22 PID [3556] 2/17/2015信息关闭连接10:04:22 AM +00：00
注意完成时间为6 ms，但是在关闭连接之前有11秒的延迟
Notice the completion time is 6 ms but then there's a 11 second lag before getting to close connection
- I我尝试删除此通话的异步，但它不帮助
- 我尝试在所有内容中使用ConfigureAwait（false）查看尝试返回原始上下文是否存在问题li>
- 我有特技d手动管理连接自己..所以打开一开始，然后在最后结束
这不能从加载因为现在有两个用户beta测试Azure站点...所以负载几乎不存在。
This can't be from load because right now there's literally 2 users beta testing the Azure site... So the load is pretty much non existent.
我已经尝试在本地重新创建，但是避风港'我不知道从这里调查问题，除了慢慢地排除EF部件之外，还有一些比较简单的方法，比如Dapper，看看这个行为是否更一致，我并不知道该去哪里。
I've tried to recreate this locally but haven't been able to... And I'm not really sure where to go from here debugging the issue besides slowly gutting the EF parts for something simpler like Dapper to see if that behaves more consistently.
技术栈：
- Sql Azure
- Azure网站扩大到两个实例
- EF 6异步功能
- web api
- Sql Azure
- Azure websites scaled up to two instances
- EF 6 Async features
- web api
推荐答案
您正在使用哪个版本的Sql Azure？
Which version of Sql Azure are you using?
我们与Sql Azure Web有类似的问题将很快退休）
We had a similar problem with Sql Azure Web (the one that will be retired soon)
我们使用Entity Framework 6查询，agsi nt一个大约70万行的表。在我的本地sql服务器上，花了一秒钟 - 在Sql Azure上花费了1分钟！更糟糕的是，有时甚至超时了
We had a query using Entity Framework 6, agsint a table with about 700,000 rows. On my local sql server it took a second - on Sql Azure it took over 1 min!, worse still sometimes it even timed out
我的猜测是，Sql azure web是一个共享的sql服务器，资源有限。
My guess is that Sql azure web is a shared sql server and that resources are limited.
这篇关于实体框架和处理查询结果的性能不一致的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
