SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS Report runs okay in Visual Studio but times out on Report Manager


SSRS Report runs okay in Visual Studio but times out on Report Manager

Author
Message
carolyn.foster
carolyn.foster
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 28

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?


Sue_H
Sue_H
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58481 Visits: 12840
carolyn.foster - Wednesday, February 21, 2018 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?



Check the SQL Server Reporting Services log file - could be some errors or warnings related to the issue.

Sue



Jeffrey Williams 3188
Jeffrey Williams 3188
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70495 Visits: 10696
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

jonathan.crawford
jonathan.crawford
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3360 Visits: 782
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
Jeffrey Williams 3188
Jeffrey Williams 3188
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70495 Visits: 10696
jonathan.crawford - Wednesday, February 21, 2018 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.

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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search