Generating Multiple queries, even if I use the same query for different Lists

[Kiran Kandavalli]

Team,

1) I created a query and this query is used by different lists in a report page.
2) when I generate "Show Generated SQL / MDX" it shows me 5 queries are executing against database.
3) So I have the followed the process as IBM suggested in this URL https://www.ibm.com/support/pages/separate-queries-created-when-same-query-used-multiple-data-containers
4) If I don't have List Totals the "Show Generated SQL / MDX" is showing me only one query.
5) But if I have List totals it is showing me different queries for each components and my report performance got degraded.

Can someone suggest me on this please.

Thanks!
Kiran

[dougp]

Can you reproduce the problem using the samples, then post your report spec?

Having multiple queries in the Generated SQL/MDX window is not causing your slowdown.  I'm pretty sure Cognos has to run a query for each object anyway.  Just because they are all defined the same doesn't mean they don't all run.

For eliminating the unnecessary queries, eliminate the unnecessary data items...
I suspect you are using one of the summarize tools in the toolbar.  Each time you do this, Cognos creates an additional data item in your query:
Total(Quantity)
Total(Quantity)1
Total(Quantity)2

Assuming these are really all the same thing (applying the total function to the Quantity column in 3 different ways) you can safely reuse one of them for all cases, even if there are different grouping layers.  (Cognos keeps it in context.)
Select the cell containing Total(Quantity)1
In the Properties pane under TEXT SOURCE find the property named "Data item value" and change it to Total(Quantity).
Do this for Total(Quantity)2.
Go to the query and delete Total(Quantity)1 and Total(Quantity)2.

Now you have a cleaner report spec.  But it probably won't be faster.  You didn't really provide much information in your initial post.  You may have multiple layers of grouping, multiple columns being totaled/summarized/..., and you may have billions of rows being returned.  ...and you're asking Cognos to perform this aggregation after it receives the data from the database server.  Also, sending your output to HTML, PDF, Excel, or CSV may provide different performance results.