Report Performence Issue

[laxmanrao83]

Hi All,

I have developeds the report using stored procedure.
In my report i have 12 cahrt and 12 crosstabs in one page.
when i run the stored proceduer in db it's taking 40 secc,but when i run the report it's taking 7- 9 mins .
Is there any way to imporve the Report preformence?

Thanks & Regards,
Laxman Rao

[Bernd Kalb]

Hi,

does each chart and crosstab use the stored procedure? Do the 40 seconds relate to ONE run of the stored procedure?

Because if it is used in all report-objects and you make a single run with 40 seconds, well, 12 times 40 seconds... guess you won't get out any quicker than it is now..

[laxmanrao83]

Yes -every corrostab and chart using one stored procedure.

For 12 carts and crosstab i am using 5 queries only.

[blom0344]

Any reason why you use an SP and not set-based logic? Would you consider 40 seconds acceptable for 1 unit of work? Unless you really need some sort of procedural logic a set-based approach using a Cognos model may be a LOT faster.

[Bernd Kalb]

Yes -every corrostab and chart using one stored procedure.

For 12 carts and crosstab i am using 5 queries only.

Doesn't matter in my opinion. 12 crosstabs = 12 times excecution of your stored procedure.

Try blom0344's solution using set-based logic instead of SP.