Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote -3 down vote favorite

There are reports build in SSRS 2008 by some user. I need to improve the performance of the already-build reports. The reports fetches a lot of data and run very very slow.

I googled and found certain steps that can be taken to improve the performance of the reports which are listed below.

Reports Side

  1. Caching of Reports

SQL Server End

  1. Creating Stored Procedure to be used in reports (reports now used free SQL Query)

  2. Removing nested views structure

  3. Creating Index on most use tables and Indexed Views.

Can you suggest possible methods/solutions which could be helpful to improve the performance of Reports in a drastically way.

share|improve this question
2  
The information provided is insufficient to say anything more than "hire an expert to fix it". Also, the question is broad enough that you could write an entire book about it (or at the very least a number of chapters), which is a classic sign of a bad question on SE. – Simon Righarts May 9 '12 at 9:53
@SimonRigharts: I'm basically focusing on the reports performance improvement. I need some useful handy tips that could change the way the report runs. – Smith Pascal Jr. May 9 '12 at 10:02
My initial tone was rather harsh, but the underlying message was "some problems can't be fixed over the internet". If you have a complex superstructure of reports and nobody's been paying attention to the server underneath it, there's not likely to be any "quick fixes". – Simon Righarts May 9 '12 at 10:15
@SimonRigharts: Never mind about tone. So you mean that the main fixes lies in database server? If yes, what should I googled about the same? – Smith Pascal Jr. May 9 '12 at 10:17

1 Answer

up vote 1 down vote

I think only you can answer this question by testing out a few things. The SQL Server End recommendation you list are really just good query-writing practice. Whether or not you cache the reports depends on how live your data is and how live your reports must be.

When I am trying to improve an SSRS report, I optimize the query (in SSMS) as much as I can (by limiting nested views and adding indexes when possible). 95% of the time, this does the trick. If it doesn't, I work with the user to come up with a caching strategy - or more specifically, we refresh the report execution snapshot overnight and just display that data all day long. This works well for reports we don't mind running overnight when the database load is low.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.