我的SSRS报表生成中的瓶颈是什么?

huangapple go评论87阅读模式
英文:

What is the bottleneck in my ssrs report generation?

问题

通过.NET控制台应用程序,我正在尝试从SQL Server报表服务器渲染相同的SSRS报表为PDF,但使用不同的参数进行几百次。每个报表的大小约为1MB。

该项目使用了与报表服务器的WCF方法:
我的SSRS报表生成中的瓶颈是什么?

使用基本的异步方法并发生成报表:

var listOfTasks = customers
	.Select(async customer =>
	{
		var parameters = new Dictionary<string, string>
		{
			{ "CustomerId", customer.Id },
			{ "BillDate", billDate }
		};
		await GenerateInvoice(parameters);
	})
	.ToList();

await Task.WhenAll(listOfTasks);
private async Task GenerateInvoice(Dictionary<string, string> parameters)
{
    var reportBytes = await _renderSsrs.ConvertToAsync(
       "\reportPath\InvoiceReport", 
       parameters, 
       FormatType.PDF);

    var filePath = $@"C:\temp\Invoices\Invoice-{parameters["CustomerId"]}.pdf";

    await CreateFileFromBytesAsync(reportBytes, filePath);
}

ConvertToAsync 的实现细节可以在此处找到:
https://gist.github.com/madcodemonkey/17216111f8ffa8d4515455fb90e1b4e9#file-program-cs

问题

最初的大约50个报告生成得相当快,但然后很快开始减速,直到最终完全停止生成更多的PDF。在SQL Server方面,显示有打开的连接,但处于休眠状态并等待命令。
我的SSRS报表生成中的瓶颈是什么?

相同报告的数据检索的时间都各不相同。
我的SSRS报表生成中的瓶颈是什么?

最终,进程以“报告执行已过期或找不到”异常停止。
我的SSRS报表生成中的瓶颈是什么?

我认为错误是来自SOAP WCF客户端的超时,与浏览器缓存无关,因为我不是在尝试通过Web浏览器打开报告。

似乎在这一点上,数据库/报表服务器成为了瓶颈,但这只是一种假设。从sp_who2中没有列出任何阻塞进程,只是连接在那里等待某些东西。分析器似乎主要是水平的,CPU活动下降。
我的SSRS报表生成中的瓶颈是什么?

我尝试将rsreportserver.config的“MaxActiveReqForOneUser”设置从20提高到50,但没有改善。

是否有人有任何建议或想法,为什么应用程序会完全停止?如果需要,我可以发布完整的代码,但我试图让这篇帖子开始变得简洁。

英文:

Via a .NET Console App, I'm attempting to render the same SSRS report as a PDF from a SQL Server Report Server a few hundred times but with different parameters. Each report is about 1 MB in size.

The project is using a WCF approach with the report server:
我的SSRS报表生成中的瓶颈是什么?

The reports are being generated concurrently using a basic async approach:

var listOfTasks = customers
	.Select(async customer =&gt;
	{
		var parameters = new Dictionary&lt;string, string&gt;
		{
			{ &quot;CustomerId&quot;, customer.Id },
			{ &quot;BillDate&quot;, billDate }
		};
		await GenerateInvoice(parameters);
	})
	.ToList();

await Task.WhenAll(listOfTasks);
private async Task GenerateInvoice(Dictionary&lt;string, string&gt; parameters)
{
    var reportBytes = await _renderSsrs.ConvertToAsync(
       &quot;\reportPath\InvoiceReport&quot;, 
       parameters, 
       FormatType.PDF);

    var filePath = $@&quot;C:\temp\Invoices\Invoice-{parameters[&quot;CustomerId&quot;]}.pdf&quot;;

    await CreateFileFromBytesAsync(reportBytes, filePath);
}

Implementation details of ConvertToAsync can be found here:
https://gist.github.com/madcodemonkey/17216111f8ffa8d4515455fb90e1b4e9#file-program-cs

Issue

The initial 50 or so reports generate fairly quickly, but then it soon begins to slow down until it slowly grinds to a complete halt and stops generating more pdfs. On the SQL Server side it shows that there are open connections but are sleeping and awaiting command.
我的SSRS报表生成中的瓶颈是什么?

The times are all over the place for the data retrieval for the same report:
我的SSRS报表生成中的瓶颈是什么?

Eventually the process stops with a "The report execution has expired or cannot be found" exception:
我的SSRS报表生成中的瓶颈是什么?

I believe the error is coming from the SOAP WCF client timing out and nothing to do with the browser cache since I am not trying to open reports through the web browser.

It seems that the database/report server is the bottleneck at this point, but its just an assumption. There isn't any blocking processes listed from sp_who2, its just the connections sitting there waiting for something. The profiler seems to level out mostly and CPU activity drops:
我的SSRS报表生成中的瓶颈是什么?

I tried bumping up the rsreportserver.config "MaxActiveReqForOneUser" setting from 20 to 50 but nothing improved.

Does anyone have any suggestions or thoughts as to why the application is grinding to a halt? I can post full code if needed, but I am attempting to keep this post succinct to start.

答案1

得分: 1

问题是您一次向服务器发出了太多请求,导致服务器资源耗尽吗?

是的。限制并发请求。在我的测试中,20个请求效果很好。可以使用类似Parallel.ForEach这样的模式来限制并发,例如:

var po = new ParallelOptions { MaxDegreeOfParallelism = 20 };
Parallel.ForEach(paramterValues, po, parameterValue =>
{
英文:

>Is the problem that you have issued too many requests to the server at once and it is running out of resources?

Yes. Limit the concurrent requests. In my testing 20 works well. Using something like Parallel.ForEach is an easy pattern for limiting concurrency, eg

var po = new ParallelOptions { MaxDegreeOfParallelism = 20 };
Parallel.ForEach(paramterValues, po, parameterValue =&gt;
{

答案2

得分: 0

罪魁祸首是 .rdl 报告数据查询本身,根据提供的参数不同,花费了相当长的时间。当我发布了数据检索时间的小样本的截图时,我没有将2和2放在一起。我错误地假设这是因为同时发送多个请求而受到影响,但事实并非如此。我优化了报告查询,现在它运行得很顺利。

英文:

The culprit was the .rdl report data queries themselves that were taking a significant amount of time depending on what parameter was provided. I didn't put 2 and 2 together when I had posted the screenshot of the small sample of the data retrieval times. I had incorrectly assumed this was being impacted by sending multiple requests at the same time, which wasn't the case. I optimized the report queries and now its running smoothly.

huangapple
  • 本文由 发表于 2023年8月10日 10:09:28
  • 转载请务必保留本文链接:https://go.coder-hub.com/76872242.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定