Compared to running reports locally (in Visual Studio) or in a Native installation of Reporting Services (SQL Server 2012 SP3) we are experiencing very frustrating performance issues when rendering reports in a SharePoint 2010 web-part.
In SharePoint we are using the SQL Server 2012 SP3 Reporting Services Add-In. We have two load balanced web servers, and two load balanced App servers in the web farm, and one database server which holds both the SharePoint databases and our 'Reporting' database.
Fro mthe execution logs I can see that the Data Retrieval and Data Processing times match when the reports are executed both locally and in Native reporting services, however the rendering times can be wildly different. 1 second locally or native, to 30-40 seconds in SharePoint.
I'm aware that in the past there has been documented problems with Report rendering when combining SharePoint 2010 and SQL Server 2008R2 – however, there's nothing to suggest this is still a widely known issue.
Our servers are given a lot of resource, more than enough, 32GB each – so I don't think it's a resource issue. I also don't think we can accept the extra overhead, in terms of added HTTP calls, is the issue. Surely it cannot make the issue that bad?
Are there any gotchas to be aware of when running this configuration? Why is running Report Services in integrated mode so bad for performance?
Best Answer
This was related to two things:
The web servers of the SharePoint farm had no external web access, so the root certificate revocation was failing (adding a delay of 15-20 seconds to paging and activity) - This was resolved by giving them access to the external web via a proxy.
The issue with extra overheads when using SSRS integrated into Sharepoint is pretty well documented and still seems to be a problem - I guess that's part of why Microsoft are disabling the Add-In in the future.