Best practice to organise queries?

[nanoha]

Hi,

I am new to Cognos and want to know how to make my reports more efficient.

Recently I have been using conditional rendering a lot. For example some calculations over daily, monthly and yearly sales etc. And I sensed that my reports sometimes run slowly.

Here comes my question, is it good practice to split my calculations in smaller queries or should I merge them into a bigger one? If I am not rendering some layouts conditionally, are the queries associated only to those layouts still running?

Thanks in advance. Any other sharing with report efficiency would be appreciated.

nanoha

[adik]

let say you have a list, if you set the property Box Type to none this will hide your list when the report is displayed but the query associated with the list report is still run.
if you set the render variable property of the list, then when the list is hidden from display the query does not run.

[Lynn]

If you look at the generated SQL from the tools menu (not within each individual query) you can get a better idea of what requests are going to the database. If you have one query that is referenced by two different layouts where each contains only a sub-set of fields in the base query you will probably see two different generated SQL statements reflecting the different behavior.

You could also compare the native SQL with the Cognos SQL to get some clues on what is being processed locally vs what is being handled by the database. If you can reduce local processing then you can often improve performance.

My understanding is that an item not rendered does not make it into the SQL statement. If the entire layout isn't rendered then the associated query will not run. If one column in a list isn't rendered then that column isn't included in the SQL that runs.

I would personally prefer a single query over several nearly identical ones. It is just easier to maintain because changes can be made in one place rather than multiple places.

Not sure I provided enough to go on, but hopefully something worthwhile to start with

Good Luck!
:)

[nanoha]

Thank you adik and Lynn! Nice explanations on conditional rendering.
Since you mentioned local processing, I also searched them a bit on the internet. But could anyone give me an example of local processing functions? I'm using DMR by the way.
Thanks!