How to reduce run time when running Report Builder report using Parameter ?

Question

Hi Team,

I have a query which runs in less than a minute in MS SQL Server but takes ~60 min to run in MS Report Builder with the same connection.

One difference I make in query is that :

for MS SQL server, where clause looks like this where Reporting_Year = '2022'

for MS Report Builder, where clause looks like this where Reporting_Year = @Year

So, I am using a Parameter called Year to get input from the user before running the report.

Is this addition of parameter really compromise the performance of the report ?

Thanks in advance

Answer 1

Hi @Prem chander

Usually report performance is affected by many factors. If you have deployed reports, you can check ExecutionLog3 to locate whether the retrieval, processing or rendering time is too long.

For improving report performance, you can check this link

https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/bb522806(v=sql.105)

https://www.mssqltips.com/sqlservertip/3659/sql-server-reporting-services-best-practices-for-performance-and-maintenance/.

Best regards,

Aniya