SQLTeam.com | Weblogs | Forums

Report generation speed

One of company reports is taking average average 30 seconds to generate during peak hours; processing about 2.3 million records. During non peak hours it is taking average 7-10 seconds. How can I speed up processing during peak hours. Where to start from; code review, wait stats; tuning advisor etc. Thanks.

I would start with a code review and optimize the process. It may need a pre-defined table built on a daily/weekly/monthly basis or just improved code.

If that doesn't resolve the issue - look into pre-generated snapshots (assuming SSRS).

If those reports are being run against production, consider moving them to a read-only secondary.

Code review and index review (which will include reviewing the query plan).

Thanks for revert. It got be near to answer.

Thanks for revert. It got me nearer to answer.

If neither optimizing code nor optimizing indexes bring the desired speed, you have to talk to your key user(s) and find out what delay between
a) snapshot of data used for report and
b) data in db
they can live with.
IF they say "real time, we want to see the current truth"
THEN they will need to provide you with better hardware
ELSE IF they accept a sensible delay which is decisively longer than report generating (you could start by proposing one hour and let the haggling go to a min. of 15 minutes) you could simply schedule the SQL code and insert results into a table that has the absolut minimum of indexes ... and most likely do truncate before inserting .... else table will grow and be able to historize the reporting data, which does not seem to be required ...

Other possible options to improve performance:
(1) page compress the data
(2) force LOB columns out of row (if 1+ LOB columns exist in the table and they are not needed by most reports, esp. the slow ones).