Custom SSRS report is slow

Client is experiencing performance issues with custom SSRS reports

• Enterprise ERP(MUNIS)

• Tyler Reporting Services

• SSRS Reports

• Performance

Performance issues in custom SSRS reports are likely caused by a suboptimal query or the processing of a large volume of records.

There’s no one-size-fits-all solution to performance issues, as multiple factors can contribute. However, a good starting point is evaluating the report’s query and leveraging temp tables to optimize execution.

For example, if the following query is slow:

SELECT *  
FROM view1  
INNER JOIN view2 ON view1.column1 = view2.column1  
WHERE view1.column2 = 0  
AND view2.column3 > '12/31/2023'  

The likely issue is the large number of records in either view1, view2, or both. Instead of filtering after the join, using temporary tables can improve performance by reducing the dataset before the join operation:

SELECT *  
INTO #tmp_view1  
FROM view1  
WHERE column2 = 0  

SELECT *  
INTO #tmp_view2  
FROM view2  
WHERE column3 > '12/31/2023'  

SELECT *  
FROM #tmp_view1 AS view1  
INNER JOIN #tmp_view2 AS view2  
ON view1.column1 = view2.column1  

By pre-filtering data into temp tables, we eliminate unnecessary rows before the join, reducing processing time and improving report performance.

If performance issues persist after applying this strategy, escalate the issue by creating a support ticket with the Enterprise ERP Reporting team for further troubleshooting.