Take the 2-minute tour ×
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.

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

closed as too broad by dezso, Jon Seigel, RolandoMySQLDBA, Mark Storey-Smith, Paul White Jul 12 '13 at 0:15

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the 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. –  Romil N 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? –  Romil N May 9 '12 at 10:17

2 Answers 2

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

You could build a cube that would prepare the report in advance, making the query for the report simpler and the retrieval of data faster. You can set a scheduled job to run when the users aren't online so that the system is under a lesser load and the execution times are faster.

just my 2 cents

share|improve this answer

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