Store Procedure based Report optimization
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 --
May 30th, 2011 1:07pm
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/
Free Windows Admin Tool Kit Click here and download it now
May 30th, 2011 2:39pm
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
May 30th, 2011 10:25pm
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
Free Windows Admin Tool Kit Click here and download it now
May 30th, 2011 10:25pm
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.
June 1st, 2011 3:36am