SSRS Optimization: Reducing Report Render Time by Eliminating tempdb Usage and SQL Formatting

By Tom Nonmacher

 

 

Note from the Field
As part of an ongoing effort to improve report performance in high usage environments, I encountered a scenario where small TSQL refinements made a significant impact. Below is a summary of how one legacy SSRS report was reduced from 15 to 20 seconds of render time down to just 6 seconds with a few targeted changes.

This report was deployed on SQL Server 2019 SSRS and had experienced delays due to inefficient SQL constructs and unforeseen tempdb usage beyond what SSRS already requires for report execution.

Performance Bottleneck: tempdb and Redundant Sorts

The original employee filter dataset included a UNION to append an "All Users" row and used an ORDER BY clause to sort the result set. This forced SQL Server to materialize a worktable in tempdb to perform deduplication and sorting. That extra load not only slowed the query but also added contention under concurrent use.

 

 
SELECT initials, fullName FROM dbo.ViewEmployee UNION SELECT NULL AS initials, 'All Users' AS fullName ORDER BY fullName

Optimized version:

 
SELECT initials, fullName FROM dbo.ViewEmployee WITH (NOLOCK) 

A note about the WITH (NOLOCK) hint: This was used in the production revision to reflect real-world implementation. In this case, the initials values were being used solely as filter input for the report and were not subject to frequent updates or transactional inconsistency. The use of NOLOCK posed no risk of materially affecting the outcome, given the static nature of the underlying data. That said, it is always important to evaluate the appropriateness of NOLOCK in your environment. Conditions still improved without the hint, but this version reflects the actual production code used.

This change, along with the removal of UNION and ORDER BY, eliminated the tempdb worktable entirely and improved performance by approximately 3 to 4 seconds.

Parameter Filter Logic: Simplified with Multivalue Parameters

The main dataset used to include this logic:

 
WHERE InitialsId = @initialsId OR @initialsId IS NULL

This was replaced with a cleaner and more efficient multivalue parameter pattern:

 
WHERE InitialsId IN (@initialsParam)
 

This change eliminated conditional branching and allowed SSRS to handle parameter expansion more naturally. While TSQL does not support true array variable types, SSRS manages multivalue parameters effectively. This improves the execution plan without needing workaround logic.

Measuring the Impact with TSQL Statistics

The following commands were used to analyze and confirm the impact of each change:

 
SET STATISTICS IO ON SET STATISTICS TIME ON -- Run query here

These helped confirm the elimination of worktable usage and improved CPU and elapsed time performance.

Formatting: Shifted from SQL to SSRS

The original query formatted and modified values in SQL using expressions like:

 
REPLACE(CustomerJobId, 'xxxx', 'Client') AS CustomerJobId, FORMAT(JobStartDate, 'MM-dd-yyyy') AS JobStartDate

This logic was removed from SQL to allow raw values to be returned:

 
CustomerJobId, JobStartDate

In SSRS, formatting was applied at the presentation layer using expressions like:

 
=Replace(Fields!CustomerJobId.Value, "xxxx", "Client") =Format(Fields!JobStartDate.Value, "MM-dd-yyyy")

This reduced SQL Server CPU usage and preserved index efficiency, accounting for an additional 2 second improvement.

ORDER BY Removed from Main Dataset

The dataset query also originally included an ORDER BY clause that was not needed. Sorting was moved to the SSRS layout layer where it belongs. This had a minor but consistent impact on rendering time and avoided unforeseen tempdb involvement.

Final Result

After all changes were applied, the report’s execution time dropped from as high as 20 seconds to a consistent 6 seconds. The improvements came from:

  • Removing the UNION and ORDER BY from the parameter dataset

  • Using efficient multivalue parameter logic

  • Eliminating formatting functions from SQL

  • Avoiding unforeseen tempdb usage beyond SSRS baseline

  • Letting SSRS handle layout and formatting responsibilities

Conclusion

This tuning session illustrates the value of separating data retrieval from presentation. SQL Server performs best when focused on filtering and returning clean data. SSRS performs best when handling layout and formatting. Combining those responsibilities leads to wasted CPU, bloated plans, and slow render times.

If you are dealing with slow SSRS reports, consider inspecting your datasets for UNIONs, ORDER BYs, and embedded formatting. Shifting those out of SQL and into SSRS may produce immediate wins.

Until next time
Tom
The Data Whisperer




19C4F3
Please enter the code from the image above in the box below.