为什么 Reporting Services 报表比查询慢得多？
我有一个查询大约需要 2 分钟才能运行。它在参数或其他方面并不是非常复杂，而且报告本身并没有进行任何真正广泛的处理。基本上只是以一种很好的格式直接输出数据。 （实际上，其中一个报告根本不格式化数据，只是返回一个用于在 Excel 中操作的平面表格。）
它也没有返回大量数据。
然而，该报告需要 30 分钟以上的时间才能完成。
什么可能导致这种情况？
顺便说一句，这是针对 SQL 2005 数据库的 SSRS 2005。
编辑：好的，我发现在报告中添加WITH (NOLOCK) 所花费的时间与通过SSMS 进行查询所花费的时间相同。如果查询来自报告服务（或本地计算机上的 Visual Studio）与来自本地计算机上的 SSMS，为什么查询的处理方式会有所不同？我看到查询在 SLEEP_WAIT 模式下在活动监视器中运行了几次，但没有被任何东西阻止...
EDIT2：连接字符串是：
数据源 = SERVERNAME;初始目录 = DBName
I have a query that takes roughly 2 minutes to run. It's not terribly complex in terms of parameters or anything, and the report itself doesn't do any truly extensive processing. Basically just spits the data straight out in a nice format. (Actually one of the reports doesn't format the data at all, just returns a flat table meant to be manipulated in excel.)
It's not returning a massive set of data either.
Yet the report takes upwards of 30 minutes to run.
What could cause this?
This is SSRS 2005 against a SQL 2005 database btw.
EDIT: OK, I found that with the addition of WITH (NOLOCK) in the report it takes the same time as the query does through SSMS. Why would the query be handled differently if it's coming from reporting services (or visual studio on my local machine) than if coming from SSMS on my local machine? I saw the query running in Activity Monitor a couple times in SLEEP_WAIT mode, but not blocked by anything...
EDIT2: The connection string is:
Data Source=SERVERNAME;Initial Catalog=DBName
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
是否肯定是查询需要很长时间才能运行，或者服务器的处理速度很慢？有些报告多次调用查询。例如，如果分页列表控件内有一个子报表，则该报表的每个页面都会单独调用查询。那么也许报告对数据做了什么操作导致了延迟？
Is it definitely the query taking a long time to run, or is the processing being done by the server that is slow? Some reports call queries multiple times. For instance, if you have a subreport inside a of a paging list control, each page of that report calls the query separately. So maybe there's something the report is doing with the data causing the delay?
您的查询返回的数据集有多大？如果它非常大，则报表服务器上花费的大部分时间可能与呈现报表所花费的时间有关。为确保您可以查看报表服务器上的 ExecutionLog 表，以查看与总体执行时间相比，TimeRendering 是否是一个很大的数字。
How large is the data set that is returned by your query? If it is very large the majority of the time that is taken on the report server could be related to the time it takes the report to render. To be sure you could look at the ExecutionLog table on the report server to see if the TimeRendering is a large number in comparison to the overall execution time.
我认为这并不罕见，但我们研究了类似的问题。
根据记忆，我们确实注意到的一件事是我们的子报表具有参数，并且我们已经配置了要从数据库查询的“可能值”。
我认为每次运行子报表时，SSRS 都会重新查询参数的可能值（即使您不使用结果，也会运行报表中的任何其他查询）。
在这种情况下，一旦我们对子报表工作正常感到满意，我们就删除了验证参数值的查询并允许“任何值”，假设父报表不会向我们提供错误的参数值。
I think that this is not uncommon, but we looked into similar issues.
From memory, one thing that we did notice was that our subreport had parameters, and we've configured the "possible values" to be queried from the database.
I think that every time the subreport runs, SSRS re-queries the possible values of the parameters (& runs any other queries in your report even if you don't use the results).
In this case, once we were happy the subreport was working OK, we removed the queries for vaidating the parameter values and allowed "any value", assuming the parent report would not feed us bad parameter values.
聚会有点晚了，但对于未来有类似问题的人来说。
参数嗅探
如果正在使用带有参数的存储过程，则可能是由于一种称为“参数嗅探”的现象造成的。
简而言之，第一次从 SSRS 执行存储过程时，会根据指定的参数值确定执行计划。然后，每次从 SSRS 执行存储过程时都会存储并使用该执行计划。即使此执行计划对于任何未来的参数值可能都不是最佳的。
要获得出色且更广泛的解释，请查看：https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server /
其他问题
也可以看看这个类似的问题：
快速查询在 SSRS 中运行缓慢
A tad late to the party, but for anybody from the future having a similar problem.
Parameter sniffing
If a stored procedure with parameters is being used, it might be due to a phenomenon called 'parameter sniffing'.
In short, the first time a stored procedure is executed from SSRS an execution plan, based on the specified parameter values, is determined. This execution plan is then stored and used every time the stored procedure is executed from SSRS. Even though this execution plan might not be optimal for any future parameter values.
For an excellent and more extensive explanation have a look at: https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/
Other questions
Also have a look at this similar question:
Fast query runs slow in SSRS