Parsing 2 SQL for 1 Query

[ciber]

Hi all,

When I have one Query to get data in a crosstab, and I create a Chart with the same Query, Cognos parse two SQL statements to the database server.
They two queries are the same, so I don't understand why Cognos use two Queries.
They report takes 2 time longer then needed I think.

Can I force Cognos to use one query for a Crosstab and Chart?

Thanks

[CognosPaul]

Using 2 objects against 1 query doesn't guarantee that it's the same query.

Cognos will generate the SQL for each object based on the fields included. A list that has, for example, Year, Month, Measure generate a different SQL than a chart that only has Year and Measure. You can check the SQL for each individual object by selecting Tools --> Show Generated SQL/MDX (not the one from inside the properties of the query).

As a side note, you can force Cognos to include fields in the SQL by selecting them in the Properties in the object properties.

[ciber]

Hi PaulM,

I know that Cognos include the fields from an object. But when I use all the same fields in a crosstab and a Chart and check the SQL (Tools-> Show Genereded SQL/MDX) the SQL is the same.

So, can I force Cognos (when 2 same SQL are genereted) to use just one?
The use exact the same fields in both objects.

Thanks.

[CognosPaul]

In the query properties, check Use Local Cache to yes.

[ciber]

Still two Queries.

Using Local Cache has nothing to do with the two queries so far as I know.

[CognosPaul]

Which version of Cognos are you using?

I used to have a paper on the way Cognos handles query reuse. I'll be looking for it, but in the meantime, this is what I've found on the experts-exchange

RWrigley: 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.

[ciber]

Hi PaulM,

Thanks for your post.

We are using cognos 8.4 (64bits).
In some situations I can't use 'use local cache' (when new records are added to the table and the report user reprompt the report, the new records are not included the query resultset......).
But the last time I checked this I was using cognos 8.1 / 8.2

I will try the option "shared-query" (if I know to find it  ;D)

If you can find the paper on the way how Cognos handles query reuse and will share it, than you make me happy :D