Hi there: I am working on a job to optimize a report. This report (we called Report A) is running extremely slow. In BIDS, it seems Report A has 7 tablixes and each one is using a different dataset which calls a different store procedure. I did a further research and seems all datasets can be broken down into the relationship of summary and details. Below are my questions: 1. If I manage to pack all 7 different store procedures into one and use one tablix to show all information, will that speed up the report execution? 2. Is there any systematic way for any report optimization job?--Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

Hi gathering SP in one can help if you are calling them thousand of times, which will minimise the number of connections , resources etc. but it will not make too much difference if you will create one SP from seven. So first suggestion is to check for optimal indexes. second is to denormalise your database/tables (not relational), and create Star schema, with Facts and Dimentions, which will increase the speed by many many times. and of course if your reports are slow changing you can create caches version of your reports but I will suggest to denormalise it (second option) Please mark this as answer if it helps. Microsoft Certified Trainer Microsoft Certified Professional Developer BizTalk blog: http://www.EnterpriseApplicationsDevelopment.com/

but it will not make too much difference if you will create one SP from seven. This is not entirely true. Performance may increase substantially if the 7 stored procedures use some common data. Moving to SSAS is a good suggestion however without knowing how the data is structured and what sort of calculations are being performed, it is difficult to know whether SSAS will assist or hinder the process. This first step should always be optimising indexes and index usage. You may find that particular indexes are not being used or there are no indexes that match your requirements. Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA Blog: Mr. Wharty's Ramblings Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker

but it will not make too much difference if you will create one SP from seven. This is not entirely true. Performance may increase substantially if the 7 stored procedures use some common data. Moving to SSAS is a good suggestion however without knowing how the data is structured and what sort of calculations are being performed, it is difficult to know whether SSAS will assist or hinder the process. This first step should always be optimising indexes and index usage. You may find that particular indexes are not being used or there are no indexes that match your requirements. Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA Blog: Mr. Wharty's Ramblings Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker

Hi cat_ca, In addition to Armen and Jeff’s replies, I would suggest you refer to the article Performance (Reporting Services) for a systematic guidance (http://msdn.microsoft.com/en-us/library/bb522786.aspx). Thanks, Tony Chain Tony Chain [MSFT CSG] | Microsoft Community Support Get or Request Code Sample from Microsoft Please remember to mark the replies as answers if they help and unmark them if they provide no help.