快速查询在 SSRS 中运行缓慢
我有一个调用存储过程的 SSRS 报告。如果我直接从查询窗口运行存储过程，它将在 2 秒内返回。但是，从 2005 SSRS 报告运行相同的查询最多需要 5 分钟才能完成。这不仅仅发生在第一次运行时，而是每次都会发生。此外，我在其他环境中没有看到同样的问题。
关于为什么 SSRS 报告在这种特定环境中运行如此缓慢的任何想法？
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.
Any ideas on why the SSRS report would run so slow in this particular environment?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（17）
你在SSRS表中使用“group by”吗？
我有一个包含 3 个字段的报告，我注意到尽管有一个简单的查询，但该报告运行速度非常慢，以至于我什至无法在搜索字段中拨打值。
然后我删除了分组，现在报告会在几秒钟内完成，一切都会立即生效。
DO you use "group by" in the SSRS table?
I had a report with 3 grouped by fields and I noticed that the report runed very slowly despite having a light query, to the point where I can't even dial values in the search field.
Than I removed the groupings and now the report goes up in seconds and everything works in an instant.
在我们的例子中，不需要任何代码。
我们服务台的注释：“清除您的互联网设置将解决此问题。”
也许这意味着“清除缓存”。
In our case, no code was required.
Note from our Help Desk: "Clearing out your Internet Setting will fix this problem."
Maybe that means "clear cache."
感谢您在这里提供的建议。我们找到了解决办法，结果确实和参数有关。由于“参数嗅探”，从 SSRS 报告执行时，SQL Server 会生成一个复杂的执行计划。解决方法是在存储过程内部声明变量并将传入参数分配给变量。然后查询使用变量而不是参数。这使得无论是从 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.
我要补充一点，我对非存储过程查询也有同样的问题 - 只是一个简单的 select 语句。为了解决这个问题，我在数据集 SQL 语句中声明了一个变量，并将其设置为等于 SSRS 参数。
多么烦人的解决方法啊！尽管如此，还是谢谢大家让我接近答案！
I will add that I had the same problem with a non-stored procedure query - just a plain select statement. To fix it, I declared a variable within the dataset SQL statement and set it equal to the SSRS parameter.
What an annoying workaround! Still, thank you all for getting me close to the answer!
将其添加到过程的末尾：
option(recompile)这将使报告的运行速度几乎与存储过程一样快
Add this to the end of your proc:
option(recompile)This will make the report run almost as fast as the stored procedure
我遇到了同样的问题，这是我对问题的描述
“我创建了一个存储过程，它将生成 2200 行，并且将在几乎 2 秒内执行，但是在从 SSRS 2008 调用存储过程并运行报告后，它实际上从未运行过并且最终我必须从任务管理器中杀死 BIDS（商业智能开发工作室）”。
我尝试过的：我尝试从reportuser登录运行SP，但该用户的SP也运行正常，我检查了Profiler，但没有任何结果。
解决方案：
实际上问题是，即使 SP 正在生成结果，但 SSRS 引擎需要时间来读取这么多行并将其渲染回来。
所以我在 SP 中添加了“WITH RECOMPILE”选项并运行了报告。这时奇迹发生了，我的问题得到了解决。
I had the same problem, here is my description of the problem
"I created a store procedure which would generate 2200 Rows and would get executed in almost 2 seconds however after calling the store procedure from SSRS 2008 and run the report it actually never ran and ultimately I have to kill the BIDS (Business Intelligence development Studio) from task manager".
What I Tried: I tried running the SP from reportuser Login but SP was running normal for that user as well, I checked Profiler but nothing worked out.
Solution:
Actually the problem is that even though SP is generating the result but SSRS engine is taking time to read these many rows and render it back.
So I added WITH RECOMPILE option in SP and ran the report .. this is when miracle happened and my problem got resolve.
我也遇到了同样的情况..非常基本的报告，SP（仅接受 1 个参数）需要 5 秒才能带回 10K 记录，但报告需要 6 分钟才能运行。根据探查器和 RS ExecutionLogStorage 表，该报告将所有时间都花在查询上。 Brian S.的评论让我找到了解决方案。我只是在SP中的AS语句之前添加了WITH RECOMPILE，现在报告时间几乎与SP执行时间相匹配。
I had the same scenario occuring..Very basic report, the SP (which only takes in 1 param) was taking 5 seconds to bring back 10K records, yet the report would take 6 minutes to run. According to profiler and the RS ExecutionLogStorage table, the report was spending all it's time on the query. Brian S.'s comment led me to the solution..I simply added WITH RECOMPILE before the AS statement in the SP, and now the report time pretty much matches the SP execution time.
我只是在 Tablix 属性中取消选择“在每个页面上重复标题列”。
I simply deselected 'Repeat header columns on each page' within the Tablix Properties.
如果您的存储过程使用链接服务器或openquery，它们可能会自行快速运行，但在 SSRS 中呈现需要很长时间。一些一般建议：
我发现问题已得到解答，我只是添加此内容以防有人遇到同样的问题。
If your stored procedure uses linked servers or openquery, they may run quickly by themselves but take a long time to render in SSRS. Some general suggestions:
I see that the question has been answered, I'm just adding this in case someone has this same issue.
我在报告检索 32000 行时遇到报告 html 输出问题。查询运行得很快，但输出到网络浏览器的速度非常慢。就我而言，我必须激活“交互式分页”以允许用户查看首页并能够生成 Excel 文件。此解决方案的优点是首页显示速度快，并且用户可以导出到 Excel 或 PDF，缺点是用户只能滚动当前页面。如果用户想查看更多内容，他/她必须使用网格上方的导航按钮。在我的例子中，用户接受了这种行为，因为导出到 Excel 更重要。
要激活“交互式分页”，您必须单击报告窗格中的空白区域，并在“属性”窗格中的报告级别上更改属性“InteractiveSize”\“Height”。将此属性设置为不同于 0 的值。在本例中，我设置为 8.5 英寸。还要确保在 Tablix 级别上取消选中“如果可能的话，在一页上保持在一起”属性（右键单击 Tablix，然后单击“Tablix 属性”，然后单击“常规”\“分页符选项”）。
I had the report html output trouble on report retrieving 32000 lines. The query ran fast but the output into web browser was very slow. In my case I had to activate “Interactive Paging” to allow user to see first page and be able to generate Excel file. The pros of this solution is that first page appears fast and user can generate export to Excel or PDF, the cons is that user can scroll only current page. If user wants to see more content he\she must use navigation buttons above the grid. In my case user accepted this behavior because the export to Excel was more important.
To activate “Interactive Paging” you must click on the free area in the report pane and change property “InteractiveSize”\ “Height” on the report level in Properties pane. Set this property to different from 0. I set to 8.5 inches in my case. Also ensure that you unchecked “Keep together on one page if possible” property on the Tablix level (right click on the Tablix, then “Tablix Properties”, then “General”\ “Page Break Options”).
我遇到了类似的问题，我的存储过程从 Management Studio 执行速度很快，但从 SSRS 执行速度非常慢。经过长时间的斗争，我通过物理删除存储过程并重新创建它解决了这个问题。我不确定其背后的逻辑，但我认为这是因为存储过程中使用的表结构发生了变化。
I came across a similar issue of my stored procedure executing quickly from Management Studio but executing very slow from SSRS. After a long struggle I solved this issue by deleting the stored procedure physically and recreating it. I am not sure of the logic behind it, but I assume it is because of the change in table structure used in the stored procedure.
我遇到了同样的问题。对我来说，这只是取消选中该选项：
如果可能的话，将SSRS 报告 。它试图将所有记录放在同一页面上，而不是创建许多页面。
I Faced the same issue. For me it was just to unckeck the option :
Of SSRS Report. It was trying to put all records on the same page instead of creating many pages.
除了参数嗅探问题之外，我发现 SSRS 在客户端处理方面通常比（在我的情况下）Crystal reports 慢。当 SSRS 引擎有很多行需要本地过滤或聚合时，它看起来就没有那么强大了。诚然，这些是结果集设计问题，经常可以解决（尽管如果需要深入了解细节，则并不总是如此），但更不成熟……报告引擎更宽容。
Aside from the parameter-sniffing issue, I've found that SSRS is generally slower at client side processing than (in my case) Crystal reports. The SSRS engine just doesn't seem as capable when it has a lot of rows to locally filter or aggregate. Granted, these are result set design problems which can frequently be addressed (though not always if the details are required for drilldown) but the more um...mature...reporting engine is more forgiving.
就我而言，我只需断开并连接 SSMS。我对查询进行了分析，执行的持续时间显示为 1 分钟，尽管查询本身的运行时间不到 2 秒。重新启动连接并再次运行，这次duration显示了正确的执行时间。
In my case, I just had to disconnect and connect the SSMS. I profiled the query and the duration of execution was showing 1 minute even though the query itself runs under 2 seconds. Restarted the connection and ran again, this time the duration showed the correct execution time.
我可以通过从底部删除 [&TotalPages] 内置字段来解决此问题。时间从几分钟缩短到不到一秒。
我无法确定的奇怪现象对总页数的计算有影响。
我正在使用 SSRS 2012。
I was able to solve this by removing the [&TotalPages] builtin field from the bottom. The time when down from minutes to less than a second.
Something odd that I could not determined was having impact on the calculation of total pages.
I was using SSRS 2012.
您可以做几件事，无需执行实际报告，只需从报告服务的数据选项卡中运行存储过程即可。还需要时间吗？
另一种选择是使用 SQL Profiler 并确定进出数据库系统的内容。
您可以做另一件事来测试它，以便重新创建一个不带任何参数的简单报告。运行报告，看看是否有所不同。您的 RS 报告可能已损坏或格式错误，从而可能导致渲染速度非常慢。
Couple of things you can do, without executing the actual report just run the sproc from within the data tab of reporting services. Does it still take time?
Another option is to use SQL Profiler and determine what is coming in and out of the database system.
Another thing you can do to test it, so to recreate a simple report without any parameters. Run the report and see if it makes a difference. It could be that your RS report is corrupted or badly formed that may cause the rendering to be really slow.
遇到了同样的问题，并通过为共享数据集提供默认参数并在报告服务器中更新该数据集来修复它。
Had the same problem, and fixed it by giving the shared dataset a default parameter and updating that dataset in the reporting server.