Search code examples
c#wcfasync-awaitreporting-services

What is the bottleneck in my ssrs report generation?

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:
Report Execution Service

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

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);
}

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.
awaiting command

The times are all over the place for the data retrieval for the same report:
Time Data Retrieval

Eventually the process stops with a "The report execution has expired or cannot be found" exception:
enter image description here

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:
profiler

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.

Solution

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