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


SSRS - Creating subscription is slow


SSRS - Creating subscription is slow

Author
Message
wannabe1
wannabe1
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1464 Visits: 497
Hello

I have this weird issue were creating a subscription takes a long time. when I click new subscription or subscribe, the page to create the subscription loads after couple of minutes. This happens only for one report. All other reports are fine. This happens both in dev and prod environments.

Is there anything that can be done to fix this? does the size of the report has anything to do with this? Please advise

Thanks
thehose_2000
thehose_2000
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 Visits: 81
I have the same problem and have not yet been able to find any answer on the web, SSRS 2008 R2. Remotely over VPN is so bad editing just a date in the subscription, well it's still waiting for a reply from the server.... it been over 10 mins... Time to kill it.. From the office it takes about 5 mins to perform any task to the subscription...

Does anyone have a idea? I'll be checking with the DBA and ask him to gather information about auto grow size and Temp DB sizing...

Love to hear some answers and/or ideas from this group,.... please and thank you....
Scott Mildenberger
Scott Mildenberger
SSChasing Mays
SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)

Group: General Forum Members
Points: 602 Visits: 662
Does the report have parameters that get their available values from datasets? If so, those datasets have to be populated when it loads. That could explain why it only affects one report - it has more (or longer queries) datasets than other report.
thehose_2000
thehose_2000
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 Visits: 81
Thank you for the response, that sounds like a good thing to test... We have a lot of subscriptions, so I was thinking that the auto size on ReportServer & ReportServerTempDB but it looks good... I think it is the report parameters that are gathering data from small datasets, which means each time I change date for example the report subscription is gathering data from those datasets each time... Reports that don't have as many parameters seem to come up faster...

I did hear a solution (if you want to call it a solution) from a Microsoft that this was a chance to use data driven subscriptions... To me that's not a solution it's a work around... Microsoft really needs to fix this because not all of us have the access and/or the room to create a dataset for a data driven subscription...

Thank you for your response
tung858
tung858
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 Visits: 381

I encountered the same issue with a report subscription where the developer created a separate dataset for each report parameter. There were close to 2 dozen parameters.

Creating or editing any of the report subscriptions would take several minutes just to open. It took just 3 of those queries to make it several minutes long.

So I ran a monitor to see which queries were taking the longest and wrote down the stored procedure name. I used Idera's SQL Diagnostic manager for this but you can also use SQL Profiler.

With the longest running queries/stored procedures captured, I proceeded to optimize and thus return the creating/editing report subscription performance back to reasonable levels.





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