Trying to speed up SSRS report with multiple stored procedures

meddojeddo 1 Reputation point
2020-09-15T08:09:32.197+00:00

I have a report which displays data for each store, and store-specific subreports. Within the subreports there are 10 stored procedures for each of the 10 components of the report. So because each subreport is store-specific, the 10 stored procedures in these subreports are running for every store and this is causing the report to run very slowly. What are the best things to look into for speeding it up?

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,914 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Lukas Yu -MSFT 5,821 Reputation points
    2020-09-16T02:02:01.363+00:00

    Hi,

    From current description, I would suggest to use less subreports in one master report. Generally too many subreport would slow the report rendering. You could try in the master report build in more dataset and tablix area.

    Also are you worry about the SP or SQL Server performance, for this aspect , you need to optimize the query in the SPs.

    You could check Executionlog3 to know if the SP sql query is too slow or the report rendering is too slow.
    Read though this article would help for the optimization direction : SSRS Reports Performance

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.