asked
How to improve performance of a report in SSRS?
Hello Everyone, AnyOne Please help me out from my problem. I have a report which have many filters in it. The data retrieval time is good, but data processing time is more. How can I improve the performance of my report? Is there any way I can reduce the filters by using them in Query? How can I change filters to Query? To improve performance of report not from query part but from design part, Are there any steps I can follow? Thanks in Advance.
It really depends.. - Does the report run via a SP/View/Dataset? - Are the filters used by the end user? - Does you have access to change the view/sp/dataset? You can query the report server database to see where the most time was taken... Query processing or rendering. If it is querying, then look at the actual query that is executed.
I am not sure if it is rendering.. But when I am viewing the report its taking lot of time. When I hard coded it and saw the data retrieval is fine. If i include filters then it takes lot of time. There are 4 groups. In Report Execution Log data processing n rendering shows more time.
If the time delay is in the Data Processing aspect, then you're looking at rewriting the query to provide the data in the format you require to minimise the data processing required, or throwing more hardware at the problem. What's the spec of the SSRS server? What else is running there?
How many records are returned to ssrs before the report filtering/grouping/sorting is applied? You should use a procedure and send the report parameters to the procedure to make sure you get as few records as possible to the report and gain benefits from indexes and SQL server capabilities, (data reduction and grouping).
1. Write a query to select only the data you require. 2. Create a stored procedure based on this query. 3. Use the stored procedure as the data source for your report. 4. Use report parameters to provide values for the procedure parameters. This will: a. reduce the amount of data travelling from your data server to your report server so speed the transfer. b. reduce the amount of data the report server has to process and format c. help your data server as it will have an execution plan for your stored procedure If your stored procedure takes a long time to run (from SSMS) then you need to look at tuning that by considering the indexing on the table, the construction of the stored procedure SQL and so forth.
