查询在查询分析器中运行很快,但在 C# 应用程序中运行缓慢

可能的重复:
sql 在 ssms 中跑得很快,在 asp.net 中很慢

使用SQL Server 2005,有几个大表的sql查询,在程序中超时.但是,当我在查询分析器中运行它时,它会在大约 30 秒内完成.

Using SQL Server 2005, there is a sql query on a few big tables and it times out in the program. However, when I run it in Query Analyzer, it finishes in about 30 seconds.

为什么不同?

推荐答案

通常这些都是 SET 设置的差异,导致方案不同.在 Management Studio 中,该过程可能使用与创建时的相同设置运行.

Usually these are SET setting differences, leading to different plans. In Management Studio the procedure is probably running with the same settings as were in place when it was created.

查看您的 ASP.Net 应用程序和 SSMS 会话的 sys.dm_exec_sessions.我会冒险猜测您的 SET 设置中至少有一个是不同的.这可能会导致不同的计划(最终这归因于参数嗅探)并且应用端通常会变得更糟.

Take a look at sys.dm_exec_sessions for your ASP.Net application and for your SSMS session. I will hazard a guess that at least one of your SET settings is different. This can contribute to different plans (ultimately this gets attributed to parameter sniffing) and the app side usually ends up worse off.

有关更多详细信息,请参阅这些其他问题:

See these other questions for a lot more details:

  • 从 Web 调用存储过程慢,从 Management Studio 调用快
  • 程序从 ADO.NET 超时但不在 SSMS 中
  • 查询从 Web 执行时超时,但从 SSMS 执行时超快
  • ADO .NET 与 SQL Server Management Studio- ADO 表现更差
  • sql 在 ssms 中运行快在 asp.net 中慢

还阅读 Erland Sommarskog 的文章,应用程序慢,SSMS 快?

And also read Erland Sommarskog's article, Slow in the Application, Fast in SSMS?

相关文章