why cognos go so slow and posible solution when make some calculation

[Enrique]

Hi everybody!

I have a query that use date for the actual month(choosed in a prompt page) and  make calculations untill  the 11 previos month.

The Problem is that ones you calculate more thatn 2 previous month the query go extrem slow.

Does anyone have a tipp or advice where should i calculate the "previous mothn" so affect the minimal posible to the Performance?


thanks in advance , Enrique

[bdbits]

I think you should stop to think why more than 2 months is "extremely slow". It sounds like this is a relational data source (answers for dimensional are different). Is it the number of records being pulled, or could it be something else? Is it a month-by-month calculation or something like a running totals aggregation?

There is just not enough information to make appropriate suggestions to resolve the performance.

[Enrique]

Hi bdbits thanks for replying ,

- yes is a month-by-month calculation , and yes again is a rational data source..

- Yes, the records are created on the fly.   (What do you want to say with "Is it the number of records being pulled" ?




Thanks for your time  and help   :)

[Francis aka khayman]

how do you calculate for the previous months?

[Enrique]

hi khayman!!

the current month is given in a Char Format  for example  "201509"
 
  1) I transform that char in a Date:     cast(substring(?Month?;1;4)||'-'||substring(?Month?;5;2)||'-01';date) 

   2) I rest one (or 2 ,3... depend of what Month i want calculate )   _add_months ([Monat_Date];-1)

   3) transform again that Previous Month date in a Char :

      substring(cast(substring(cast([Previous_Date];char(7));1;4)||substring(cast([Previous_Date];char(7));6;2);char(6));1;6 )



Why ?? does it matter?


Thanks !!

[bdbits]

What do you want to say with "Is it the number of records being pulled" ?

Sorry, I mean how many rows are being queried. Obviously this can have a tremendous effect on performance.

Your expressions for calculating months may also be having a significant effect, particularly all the casts and substrings. These are probably getting passed to the where clause and being evaluated for every row, and if it is a lot of rows this could be a significant part of the problem. If it is a possibility you might consider creating some additional columns in the database to hold some of the values you are calculating on the fly. This can save you loads of time if you do calculations like this often. For example, you could hold the char formatted date in an actual date column, or break out the year and month numbers into a column so you can eliminate some of the substrings and casts.

I would probably take the SQL being generated and put it directly into a native database query tool (e.g. PL/SQL Developer for Oracle or SSMS for SQL Server), check the performance there, check the explain plan, and maybe have a talk with your DBA if that is possible.

[Enrique]

thank you very much bdbits !! I really apreciate your help!!  :D

I ll see what can i do.