SSRS Performance with multiple datasets

Question

I have two major questions on the SSRS performance. I am in the process of consolidating 10 SSRS report (10 summary RDLs and 10 Detail RDLs with each report having ~4 tablix). Each report hits a stored procedure to get the results. Now we have consolidated all tablixs into one RDL and have created a new parameter where user can select which report he wants to open and accordingly that tablix is displayed through visibility property. Now in terms of performance I want to understand whats better. Questions Below:

After consolidation, In one RDL we have 10 datasets hitting 10 different procedure. Should I consolidate all the procedures into one and run the section of the procedure as per user selection of report from parameter using IF Else conditions. Example User selects report A then in the procedure IF @report = A --Code for A-- ELSE IF @report = B --Code for B--. If I do this, will I be able to maintain cache for each of the 10 reports.?
For the 10 detail RDLs which again has its own 10 different detail stored procedures. Is it a good idea to consolidate all the detail Tablixs into one and show/hide the tablix as per the user selection of the report from report parameter. Basically, I will pass the report name as a parameter through Action property and then show/hide tablixs with IIF conditions. OR should I keep 10 different RDLs for detail reports. I have that option open. What will be better for performance.

Option 2 would not prevent all of the SQL code from being executed, right? If that's correct, then i suppose it depends. i.e. it depends on how big / fast your SQL Server is. With this amount of complexity, I would just test both scenarios. — scsimon
So for question 2 on the detail RDLs. I have 10 different datasets hitting 10 different store procedures. Tell me please, if I consolidate all detail tablix into one, keep the datasets separate and hide the tablix as per user selection of report. Will all the detail datasets still run concurrently irrespective of the tablix being hidden? whats better to maintain to cache. One dataset with IF ELSE condition in procedure OR10 different datasets — Jatin Garg
Right, but you are only hiding the results from the user, right? You are still returning the results to the report server, so the work is being needlessly done it seems. — scsimon
Okay so I think it will be better to keep the RDLs separate. OR I could consolidate them one including all stored procedures and use IF ELSE condition in the stored procedures to run the section of the code. That will only run the code for the specific report. Thoughts on it? — Jatin Garg
Combining the stored proc seems logical, assuming the user isn't going to select one option and then turn around and select another option and get frustrated waiting on the data to return to the client. If the business case wouldn't cause for that, then I would combine the procs or just write a wrapper procedure that, based on the user input, executes the appropriate procedure. Either way would work I think — scsimon

Harry Harry · Accepted Answer · 2019-03-19T19:50:24

what I would do is - assuming the 10 RDL are running 10 different stored procedures, assign each stored procedure a hidden parameter. lets call it @report_number (for example). So for report one, in the stored procedure, add a where clause to include and @report_number = 1 this should technically stop the stored procedure running as soon as it sees a different number passed (say 2 for report 2).. so on and so forth..

The @report_number is assigned in a separate dataset.
e.g.

report1 - value = 1

report2 - value = 2

.

report10 - value = 10

so when user runs report 1, then the value 1 for @report_number is passed to that dataset.. the rest of the datasets shouldn't run the procedure associated with it as they won't have the expected value in the where clause.

Hope that made sense.

SSRS Performance with multiple datasets

1 Answers