Hi,
I would like to understand how Cognos manage the queries, as I've seen that (in a test) it's calling a query 3 times instead of what I was thinking -> calling the query one time and "working" with the datas in order to create the objects needed.
I try to explain:
. let's say I have 3 objects in Cognos: a PageSet, 2 Pages depending on this PageSet.
They are all related to a query, the same, lets say "query1".
As the query is a call to a table function in Oracle, I've "Insert" actions in some tables (for admin reasons) in the SQL before retrieving the datas for Cognos, so I can clearly see that Cognos makes 3 calls to the query, one call per object to create.
Is there some settings for the query object in Cognos in order to avoid such a behaviour ?
(I fear that, in the end, it could be bad for the performances, even if now, I can't see problems)
Is it a "normal" behaviour ?
Thanks.
Vinc.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Business Accounts
Answer for Membership
by: RWrigleyPosted on 2009-01-22 at 15:43:13ID: 23444989
Yup, that's how Cognos works. Cognos 8 is designed as a query tool, not a batch processing tool, thus it is usually easier (and more efficient) to generate multiple queries that can be sorted, grouped and aggregated as needed for each report object. For something like a page set, Cognos will create a cursor using a sql that contains parameter markers. This allows the database to reuse the execution plan, so it doesn't have to re-process the query itself.
Cognos 8 does have the ability to reuse query result sets (by enabling the "use local cache" option on the query in report studio), but this ability assumes that the grouping and sorting options are the same across all the queries. However, if the report contains Crosstabs or charts, the cache won't be reused.
Cognos 8.4 (the latest version) introduced shared-query functionality that can allow a single query to be used by multiple report objects without requerying, but I haven't played with it yet, so I don't know how well it works for page-set or burst reports.