实体框架 4 - 存储过程异常缓慢
当从 Management Studio 执行时，存储过程在 <0.1 秒内完成，但当通过 EF 执行时，需要 2 秒以上。
下面是分析器输出：
有点讽刺，因为创建 SP 的原因是为了提高 EF 查询的性能花费了大约1.2秒。
更新 不管它的价值如何，SP 结果都会映射到 EF 复杂类型。我调用时没有任何诡计：
var menuTags = db.GetMenuTags(2, "en-US");
EF 生成的 SQL 是：
exec [dbo].[GetMenuTags] @CustTypeId=2,@LanguageId='en-US '
唯一的区别是 EF 在 @LanguageId 末尾添加了一堆空格，但它们不影响性能。我在 MSSMS 中尝试了完全相同的 SQL，它的工作效果与没有空格一样好。
When executed from Management Studio the stored procedure finishes in <0.1s, but when executed via EF it takes over 2s.
Below is profiler output:
A bit ironic since the reason for creating the SP was to improve performance on an EF query that took around 1.2s.
UPDATE
For whatever it is worth, the SP result is mapped to an EF complex type. I call without any hocus pocus:
var menuTags = db.GetMenuTags(2, "en-US");
The SQL generated by EF is:
exec [dbo].[GetMenuTags] @CustTypeId=2,@LanguageId='en-US '
The only difference is a bunch of blank spaces EF adds to the end of @LanguageId, but they don't affect the performance. I have tried the exact same SQL in MSSMS and it works just as well as without the blank spaces.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
通过 EF 执行的 SP 总是返回所有行，这可能是延迟的原因。
也许发布您的 SP 和原始 EF 生成的 SQL 将有助于
编辑以添加似乎是解决方案的内容
另一个想法（从过去的日子来看）参数嗅探通常是已运行的 SP 缓慢的罪魁祸首由具有错误参数的开发人员创建，然后设置蹩脚的执行计划。添加“WITH RECOMPILE”选项来获取基于合适参数的新计划将在很大程度上解决此问题。另一个作弊是将传入的参数分配给本地 SQL 变量（SP 本地），并且仅在查询中使用本地变量。
现在你有了一个具有良好执行计划的 SP，你应该能够删除“WITH RECOMPILE”，它仍然可以工作。我想说，参数嗅探问题很少见。
SPs executed via EF always return all rows, could that be the delay.
Maybe posting your SP and the original EF generated SQL will help
Edited to add what appears to be the solution
Another thought (from days gone by) Parameter sniffing was oft the culprit for a slow SP which had been run by a developer with bad parameters and then set up a crappy execution plan. Adding the WITH RECOMPILE option to get a new plan based on decent parameters would resolve this problem mostly. Another cheat was to assign the passed in parameters into local sql variables (local to the SP) and only use local variables in the query.
Now you have the SP with a decent execution plan you should be able to remove the 'WITH RECOMPILE' and it'll still work. Parameter Sniffing problems are rare I would say.