how to improve performance issue of a chart report as the design of report is ha

[xyz]

Hi All,

I have a chart report whose performance is taking more than 30 seconds, I am using Cognos 10.2.1 and data source is relational model. The requirement of the chart is it has to show Open cases and Closed cases as lines and bar chart with cumulative difference between Open cases and Closed cases. And display Month concatenated with year. I have extracted Month and year from date column.

The design I have used is for Open cases I have used one query and for closed cases I have used second query and union them and did the running - total in the final union query. Individual queries are running with in 10 seconds, after union performance is dropped with including running-total. How will I make the performance improvement.

Open cases query used create date date column, Closed cases query uses resolution date date column.
I tried by having in the single query.

Can you please let me know your suggestion, your help is much appreciated.

Thanks & Regards,
XYZ

[navissar]

Running a running-total on the results of a union prevents the database, usually, from using the tables' indices in as much as there are any. Why can't you query the data in one go?
Also, depending on query volume, 10 seconds per query can be a long time. Check the tables are well indexed and, if necessary, partitioned.
I'd also look into adding the running total to the data warehouse, if possible.

[xyz]

Hi Nimrod,

Thank you very much for the suggestion.



Thanks & Regards,
XYZ