How to improve the query performance for my reports?

Hi, all here,

Thank you very much for your kind attention.

I am having a problem with the performance of my reports. The data for my reports is retrieved from two large transaction joined tables thus resulting in the poor qeury performance for my reports. How can I effectively improve the query performance? It is that I'd better to create a data view for the report data to retrive the data for the reports based on the data view? Or what is the best pratice for it?

Thanks a lot in advance for any guidance and help.

With best regards,

Yours sincerely,

This is the same as tuning any SQL. Drop the SQL from your dataset into SQL Server Management Studio in a New Query and check the Include Actual Execution Plan. Run the query and look at the execution plan. If you see any 'Table Scans' look for ways to eliminate them (different join criteria, different select criteria, create new indexes, etc). If you are using parameters you may need to 'hard code them' or declare SQL variables for them (I prefer the later so the query can be dropped right back into reporting services once I get it tuned).

|||

Hi, Lonnie, thank you very much for your advices.

With best regards,

Yours sincerely,

|||Further to Lonnie's good advice I would also suggest as a general good practice returning as little data to RS as possible. Utilise parameters to filter the query, use a group by to presummarise the data if possible. RS will be performign further processing of your data in ReportServerTempDB for report formatting and paging so reducing the amount of data it needs to deal with can have a huge impact on performance.