December 18, 2012 at 5:41 am
I have a report that has parameters(some cascading) based on Share Datasets.
When opening this report on our production report server it takes +/- 30 seconds before SQL statements start appearing in Profiler. The sql statements take anyware between the 10-50 ms to complete.
On the Development and Test Report Servers there is no problemen.
It seems that the SQL preparation takes an extremely long time to complete. Google search hasn't led to any results. Other reports defined by hardcoded SQL statements in the Parameters it's not a problemen.
For our customer's it seems like a long time before they can click on View Report which is very frustrating.:crying:
Anybody experience with this issue?
December 18, 2012 at 6:08 am
If its 2008 R2 have you tried caching the shared datasets so that it doesnt have to prepare and execute then each time the report is accessed?
http://msdn.microsoft.com/en-us/library/ff487452%28v=sql.105%29.aspx
December 18, 2012 at 6:11 am
anthony.green (12/18/2012)
If its 2008 R2 have you tried caching the shared datasets so that it doesnt have to prepare and execute then each time the report is accessed?http://msdn.microsoft.com/en-us/library/ff487452%28v=sql.105%29.aspx
For a couple of shared datasets this would be possible. Could the size of the RDL (2MB) be an issue?
December 18, 2012 at 6:20 am
It would depend on the load on the SSRS server as the XML will need to check that all depended objects exist first before allowing you to actually look at the report, before it starts to execute the data sets.
The size maybe an issue, I have never seen a RDL that big in my time using SSRS but others may have.
December 18, 2012 at 6:31 am
Check if XML is valid sounds like a valid problem. Also have this posted at the MSDN site. Will give a reply if I get a response over there.
If anybody else has an idea, leave a message.
January 15, 2013 at 9:00 am
The problem was the total numbers of users that where authorized for the Shared Datasets(SD). In our situation it was 3885 users. When only one user is authorized for the Shared Dataset the performance is no problem. It seems that everytime a shared dataset is executed, the authorization is checked.
Solution: Define a custom security extension and place the users in a database that connect through one user specified on the SD on the Report Server(SSRS).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply