问题描述
我有一个调用存储过程的 SSRS 报告.如果我直接从查询窗口运行存储过程，它将在 2 秒内返回.但是，从 2005 SSRS 报告运行的相同查询最多需要 5 分钟才能完成.这不仅发生在第一次运行时，它每次都会发生.此外，我在其他环境中没有看到同样的问题.
I have an SSRS report that calls out to a stored procedure. If I run the stored procedure directly from a query window, it will return in under 2 seconds. However, the same query run from an 2005 SSRS report takes up to 5 minutes to complete. This is not just happening on the first run, it happens every time. Additionally, I don't see this same problem in other environments.
关于为什么 SSRS 报告在这种特定环境中运行如此缓慢的任何想法?
Any ideas on why the SSRS report would run so slow in this particular environment?
推荐答案
感谢您在此处提供的建议.我们找到了一个解决方案，结果证明它与参数有关.由于参数嗅探"，SQL Server 在从 SSRS 报告执行时生成了一个复杂的执行计划.解决方法是在存储过程中声明变量并将传入参数分配给变量.然后查询使用变量而不是参数.无论是从 SQL Server 管理器调用还是通过 SSRS 报告调用，这都会导致查询执行一致.
Thanks for the suggestions provided here. We have found a solution and it did turn out to be related to the parameters. SQL Server was producing a convoluted execution plan when executed from the SSRS report due to 'parameter sniffing'. The workaround was to declare variables inside of the stored procedure and assign the incoming parameters to the variables. Then the query used the variables rather than the parameters. This caused the query to perform consistently whether called from SQL Server Manager or through the SSRS report.
这篇关于快速查询在 SSRS 中运行缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！