Hi all,

I have several SSRS reports whose primary datasets use stored procedures as the query type. 

When you create a dataset and choose a stored procedure for your query, SSRS automatically creates fields using all the columns that are returned by your stored procedure.

However, in most cases, I only want to use a subset of those automatically created fields in my report. Would it be worth my time to go through my reports and remove the unnecessary fields from the dataset? That is, would I get any kind of performance boost? Does the presence of the unnecessary fields hurt performance?

Thanks for your thoughts.

Dylan

Hi Dylan,

According to your description, you have created a dataset to retrieve data, but the report only uses part of the result set within the dataset. Now, you want to know if it can cause performance issue by retrieving both used and unused data in the report, right?

In Reporting Services, the total time to generate a report include TimeDataRetreval, TimeProcessing and TimeRendering. TimeDataRetrieval is the time needed for SQL Server to retrieve the data of all datasets in the report. This is time spent openings connections to the data source and time spent reading data rows from the data extension. All datasets defined in the report will be retrieved. We can find all those information in Executionlog3 view in in the ReportServer database. Then we can optimize the report to improve performance based on this article: Troubleshooting Reports: Report Performance.

In your scenario, since the dataset contains unused data, you can modify the stored procedure or use query to retrieve useful data. It might save a little time compare to retrieving both used and unused data.

If you have any question, please feel free to ask.

Best regards,
Qiuyun Yu