不同的执行时间Application vs SSMS [英] Different execution time Application vs SSMS
问题描述
我正在尝试优化一些linqToEntities查询。
I'm trying to optimize some linqToEntities queries.
在此过程中，突然一个查询开始采取执行8分钟。
During the process, suddenly one query started to take 8 minutes to execute.
但是，如果我在SQL事件探查器上捕获查询并在SSMS上执行查询，则只需1秒。
However, if I capture the query on SQL Profiler and execute the query on SSMS, it takes only 1 sec.
我可以确认在SQL事件探查器中捕获批处理启动/批处理完成事件的执行时间（8分钟）。
I can confirm the execution time (8 minutes) capturing the batch starting/batch completed events in SQL Profiler.
我正在测试环境中执行，没有锁定查询。我清除了计划缓存，查询从应用程序获得不同的执行计划是不可能的。
I'm executing on a test environment, no locks holding the query. I cleared the plan cache, It's quite impossible that the query is getting a different execution plan from the application.
最后，我设法将其恢复到2秒：问题是在优化过程中，我决定将查询结果投影到匿名类型，这会将查询从1秒提高到8分钟。
Finally, I managed to get it back to 2 sec.: The problem is that, during the optimization, I decided to project the result of the query to an anonymous type and this raised the query from 1 sec to 8 min.
当我回到最后的现有类时在投影上，它回到了2秒。不同之处在于最终的课程需要在投影期间进行一些计算。当这个
发生时，我打算从SQL执行中删除这些计算。
When I got back with the final existing class on the projection, it got back to 2 sec. The difference is because the final class requires some calculations during the projection. I was planning to remove these calculations from the SQL execution when this happened.
所以，问题是：为什么使用匿名类型进行投影可以将执行时间从1秒提高到8分钟？
So, the question is: Why using an anonymous type for projection can raise the execution time from 1sec to 8 min.?
记录金额为32K。
提前谢谢！
Dennes
推荐答案
但是，如果我在SQL事件探查器上捕获查询并在SSMS上执行查询，则只需1秒。
没有对象实现ORM的虚拟对象模型中的对象发生在对象
物化化速度降低的情况下。
No object materialization with objects off of the ORM's virtual object model is taking place with object materialization slowing things down.
当我回到投影的最终现有课程时，它又回到了2秒。不同之处在于最终的课程需要在投影期间进行一些计算。我计划在发生这种情况时从
SQL执行中删除这些计算。
听起来你应该使用混凝土投影类似于没有业务逻辑的DTO，因为我假设你知道假设在DTO中的公共属性类型。如果需要，您可以使用业务对象
对DTO采取行动。
It sounds to me that you should be projecting using a concrete type like a DTO that has no business logic in it, since I would assume that you know the public property types that as suppose to be within the DTO. You act upon the DTO with a business object if need be.
https://en.wikipedia.org/wiki/Data_transfer_object
https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp
因此，问题是：为什么使用匿名类型进行投影可以提高执行力时间从1秒到8分钟。
So, the question is: Why using an anonymous type for projection can raise the execution time from 1sec to 8 min.?
我不知道也许您应该在投影中使用自定义类型，例如DTO。
I don't know and maybe you should just use a custom type in the projection, like a DTO.
这篇关于不同的执行时间Application vs SSMS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
