blocks|key|1110419|text|感谢您在这里提供的建议。我们已经找到了一个解决方案，而且它确实与参数有关。由于“参数嗅探”，SQL+Server在从SSRS报告执行时会生成复杂的执行计划。解决方法是在存储过程中声明变量，并将传入的参数分配给变量。然后查询使用变量而不是参数。无论是从SQL+Server管理器调用还是通过SSRS报告调用，这都会导致查询执行一致。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1110420|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|1325682|text|将下面这段代码添加到proc的末尾：option(recompile)|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1325683|这将使报告的运行速度几乎与存储过程一样快|1325684|entityMap^0|I|H|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|M|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|N|8|@]|D|@]|E|$]]]|I|$]]

Add this to the end of your proc: <code>option(recompile)</code>

This will make the report run almost as fast as the stored procedure

blocks|key|1325803|text|我要补充的是，我在使用非存储过程查询时也遇到了同样的问题--只是一个普通的select语句。为了解决这个问题，我在dataset+SQL语句中声明了一个变量，并将其设置为等于SSRS参数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1325804|多么烦人的变通方法！不过，还是要感谢你们让我离答案更近了！|1325805|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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!

blocks|key|1110784|text|我也有同样的问题，下面是我对这个问题的描述|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1110785|“我创建了一个存储过程，它将生成2200行，并在几乎2秒内执行，但是在从SSRS+2008调用存储过程并运行报告后，它实际上从未运行过，最终我不得不从任务管理器中终止BIDS+(Business+Intelligence+development+Studio)”。|1110786|我尝试的方式:我尝试从reportuser+Login运行SP，但SP对于该用户也运行正常，我检查了Profiler，但没有问题。|1110787|解决方案：|1110788|实际上，问题在于，尽管SP正在生成结果，但是SSRS引擎需要花费时间来读取这些行并将其渲染回来。因此我在SP中添加了WITH+RECOMPILE选项，并运行了报告。这就是奇迹发生的时候，我的问题得到了解决。|1110789|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|O|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

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.

blocks|key|1110571|text|我有相同的场景occuring..Very+basic报告，SP+(仅接受1个参数)需要5秒才能恢复10K记录，而该报告需要6分钟才能运行。根据profiler和RS+ExecutionLogStorage表，报告将所有时间都花在了查询上。Brian+S.的评论引导我找到了解决方案。我只是在SP中的AS语句之前添加了WITH+RECOMPILE，现在报告时间几乎与SP的执行时间相匹配。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1110572|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|1110672|text|我只是在Tablix属性中取消选择了“在每个页面上重复标题列”。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1110673|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I simply deselected 'Repeat header columns on each page' within the Tablix Properties.

blocks|key|1326141|text|我在检索32000行的报告时遇到了报告html输出问题。查询运行得很快，但输出到web浏览器的速度非常慢。在我的情况下，我必须激活“交互式分页”，以允许用户看到第一页并能够生成Excel文件。这种解决方案的优点是第一页显示速度快，用户可以生成导出到Excel或PDF，缺点是用户只能滚动当前页面。如果用户想要查看更多内容，他\她必须使用网格上方的导航按钮。在我的例子中，用户接受了这个行为，因为导出到Excel更为重要。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1326142|要激活“交互式分页”，您必须单击报表窗格中的空白区，并在属性窗格中更改报表级别上的属性“InteractiveSize”\“Height”。将此属性设置为不同于0。我把我的箱子设成了8.5英寸。还要确保在Tablix级别上取消选中“如果可能，在一页上保持在一起”属性(右键单击Tablix，然后单击“Tablix+Properties”，然后单击“General”\“page+Break+Options”)。|1326143|​|1326144|📷|atomic|offset|length|1326145|1326146|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/e409bf3c4601cce0cd94f9bb0b2adc65.jpeg|imageAlt^0|0|0|0|0|1|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@$I|Y|J|Z|1|10]]|A|$]]|$1|K|3|E|5|6|7|11|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|M|$N|$5|O|P|Q|A|$R|S|T|-4]]]]

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”).

<a href="https://i.stack.imgur.com/fD6CT.jpg" rel="noreferrer"><img src="https://i.stack.imgur.com/fD6CT.jpg" alt="enter image description here"></a>

blocks|key|1110471|text|我遇到了一个类似的问题，即我的存储过程在Management+Studio中执行得很快，但在SSRS中执行得非常慢。经过长时间的努力，我通过物理删除存储过程并重新创建它解决了这个问题。我不确定它背后的逻辑，但我认为这是因为存储过程中使用的表结构发生了变化。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1110472|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|1110845|text|抛开参数嗅探问题不谈，我发现SSRS的客户端处理速度通常比Crystal+reports慢(在我的例子中)。当SSRS引擎有许多行要在本地过滤或聚合时，它看起来就不那么有能力了。诚然，这些都是经常可以解决的结果集设计问题(虽然不总是需要详细信息才能下钻)，但um...mature...reporting引擎越宽松。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1110846|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|1111204|text|在我的例子中，我只需要断开并连接SSMS。我分析了查询，执行时间显示为1分钟，即使查询本身运行不到2秒。重新启动连接并再次运行，这一次持续时间显示了正确的执行时间。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1111205|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|1325553|text|在不执行实际报表的情况下，您可以做一些事情，只需从reporting+services的data选项卡中运行存储过程即可。还需要时间吗？另一种选择是使用SQL事件探查器，并确定进出数据库系统的内容。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1325554|您可以做的另一件事是测试它，以便重新创建一个不带任何参数的简单报告。运行报告，看看是否有所不同。这可能是您的RS报告已损坏或格式错误，这可能会导致呈现非常缓慢。|1325555|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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.

blocks|key|1110883|text|具有相同的问题，并通过为共享数据集提供默认参数并在报表服务器中更新该数据集来修复此问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1110884|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Had the same problem, and fixed it by giving the shared dataset a default parameter and updating that dataset in the reporting server.

blocks|key|1111015|text|您是否在SSRS表中使用"group+by“？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1111016|我有一个包含3个按字段分组的报告，我注意到该报告运行非常慢，尽管有一个简单的查询，以至于我甚至不能在搜索字段中拨号值。|1111017|然后我删除了分组，现在报告在几秒钟内就会出现，所有的工作都在瞬间完成。|1111018|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

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.

blocks|key|1111130|text|我可以通过从底部删除&TotalPages内置字段来解决这个问题。从几分钟减少到不到一秒的时间。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1111131|一些我不能确定的奇怪的事情对总页面的计算产生了影响。|1111132|我使用的是SSRS+2012。|1111133|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

I was able to solve this by removing the [&amp;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.

blocks|key|1326270|text|在我们的例子中，不需要代码。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1326271|我们服务台的注释：“清除您的Internet设置将解决此问题。”|1326272|也许这意味着“清除缓存”。|1326273|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

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."

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?

Fast query runs slow in SSRS

我有一个调用存储过程的SSRS报告。如果我直接从查询窗口运行存储过程，它将在2秒内返回。但是，从2005年SSRS报告运行相同的查询最多需要5分钟才能完成。这不仅仅在第一次运行时发生，它每次都会发生。此外，我在其他环境中看不到同样的问题。你知道为什么SSRS报告在这个特定的环境中会运行这么慢吗？

问快速查询在SSRS中运行缓慢
EN

回答 15

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问快速查询在SSRS中运行缓慢EN

回答 15

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问快速查询在SSRS中运行缓慢
EN