February 21, 2018 at 8:12 am
We currently have a report in SSRS that runs within 2 minutes in Visual Studio, but when we deploy the report to the Report Manager, the report is just staying on Loading.
Can anyone advise how we can check what is causing the execution issue?
February 21, 2018 at 10:30 am
carolyn.foster - Wednesday, February 21, 2018 8:12 AMWe currently have a report in SSRS that runs within 2 minutes in Visual Studio, but when we deploy the report to the Report Manager, the report is just staying on Loading.
Can anyone advise how we can check what is causing the execution issue?
Check the SQL Server Reporting Services log file - could be some errors or warnings related to the issue.
Sue
February 21, 2018 at 12:53 pm
First - are you sure it only takes 2 minutes to execute in SSDT? SSDT caches the dataset and runs the reports from the cached data - so after the first execution SSDT is not really executing the code again to get the data.
Second - you need to verify the connection settings between your workstation and SQL Server compared to SSRS to SQL Server. These settings could cause different execution plans to be generated causing the difference in execution times.
And finally - review the code and see if it can be optimized. If there is no way to optimize the SQL code to execute faster - then you may want to consider either a shared dataset or generating the report as a snapshot instead of executing the code every time.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 21, 2018 at 2:48 pm
lastly, I've had the issue where it was cleared up by creating an internal variable within the stored proc behind the report, and passing in the input to the internal variable instead of the input variable. I have absolutely no idea why that helped, but it was night and day.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
February 22, 2018 at 12:34 pm
jonathan.crawford - Wednesday, February 21, 2018 2:48 PMlastly, I've had the issue where it was cleared up by creating an internal variable within the stored proc behind the report, and passing in the input to the internal variable instead of the input variable. I have absolutely no idea why that helped, but it was night and day.
This is a parameter sniffing related issue - and that can solve the performance problem. It would also indicate to me that there are two (or more) plans for that procedure - one for logging into SSMS and a separate one coming from SSRS.
You can also get separate execution plans by simply using a different login/user. If the objects in the code are not schema qualified - and each user has a different default schema defined then a separate execution plan will be generated for each user schema that calls the procedure.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply