SSRS 2008 R2 reports generating slow (SQL prepare?)

Question

SSRS 2008 R2 reports generating slow (SQL prepare?)

Henk Geurs

SSC Veteran

Points: 295
More actions
December 18, 2012 at 5:41 am

#268351

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?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113499 More actions · Answer 1

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

Henk Geurs SSC Veteran Points: 295 More actions · Answer 2

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?

Ant-Green SSC Guru Points: 113499 More actions · Answer 3

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.

Henk Geurs SSC Veteran Points: 295 More actions · Answer 4

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.

Henk Geurs SSC Veteran Points: 295 More actions · Answer 5

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).