Stored Procedure being called many times - Cognos 10.2 RS

[CoolP]

Hi,


We have stored procedure that is getting results in 40 seconds when executed from the DB client. A certain portion of the stored procedure result set is used to display multiple objects in the report studio report. (ex:- charts and crosstab with different filter crteria).


The report is taking roughly around 9 minutes to run. When i looked into the details, I can see that cognos is calling the SP for each and every object once and this is causing the issue.


Is there a way to modify this and make sure that the SP is executed only once and it's results are brought into the cognos app tier and then the filters are applied on this result set to cater for the charts and crosstabs?


I have tried setting the query properties "Use Local Cache" to Yes and also "Processing" to Limited Local but no luck.


Thanks
Prasan

[CognosPaul]

In the query explorer, drag in a new query and drag the SP query to the right of that. In theory, this should tell Cognos to fetch the data from the SP once, and simply requery the stored data in that new query.

[CoolP]

Hi Paul,

This alone didn't do the trick. Apart from this I made an entry in  CQEConfig.xml.

"Within the CQEConfig.xml file locate the text <section name="QueryEngine"> and add <entry name="RequestHintLocalCacheHasHigherPriority" value="0"/> on a new line underneath the <section name="QueryEngine"> section.

Thanks
CoolP

[MFGF]

As far as I'm aware, a query in a report is simply a specification of how to retrieve data. If you have multiple objects based on data from that query, they will each retrieve the data they need independently.

If you switch to using Dynamic Query Mode, in theory the first retrieval should fetch and cache the data, and subsequent requests for that data should be fulfilled from the cache.

Cheers!

MF.