Problem of performance when using time function on a filter

[backhandx]

Hi all,

I have a problem of performance when executing a report.
When I am using a function in a filter like this: [date]= make_timestamp(_year(?date?)-1;10;31) the report takes 1 hour to run,
but if I write the date in hard like this: [date]= '2015-10-31' the reports runs in olny 3 minutes.

I am wondering why this huge delay when I use time function in a filter?

Could anyone give me a hint !

Thanks

[MFGF]

Hi all,

I have a problem of performance when executing a report.
When I am using a function in a filter like this: [date]= make_timestamp(_year(?date?)-1;10;31) the report takes 1 hour to run,
but if I write the date in hard like this: [date]= '2015-10-31' the reports runs in olny 3 minutes.

I am wondering why this huge delay when I use time function in a filter?

Could anyone give me a hint !

Thanks

My guess is that because you are mixing database and Cognos inbuilt functions, you are forcing local processing to occur, which means the report is bringing back the entire data set to the Cognos server, then filtering locally.

Have you examined the SQL being generated by the report and compared what is being generated in each of the scenarios above?

Perhaps try an approach using just Cognos functions, eg

[date] = _make_timestamp(extract(year,?date?)-1,10,31)

Cheers!

MF.

[backhandx]

Thanks  for you reply.

The extract function does not work, it takes long time to run  :/

I am wondering why time_stamp is not a cognos function even if it is mentionned in the available function in the filter box ?

[Lynn]

Have you examined the SQL being generated by the report and compared what is being generated in each of the scenarios above?

It looks like you are comparing a date with a time stamp which are two different data types. What if you cast your manufactured timestamp to a date?

You should still compare the SQL to better understand what is going on.

Every database has different functions that are supported beyond the ANSI standard stuff. Cognos functions will often translate the SQL to make use of these. In other cases Cognos will perform the processing locally. When you compare the native SQL to the Cognos SQL you can often see where the database is handling something vs. the Cognos server. It is always best to get the database to do the heavy lifting where possible since it will be more efficient than the Cognos server.

It is possible to reference database functions that have no Cognos function equivalent. In these cases it can be tricky if you migrate to a different database vendor that doesn't offer that same function with the same syntax. Cognos functions can insulate a report from these types of migration concerns.

[vinu_rnr]

Hi,

i had encountered sort of similar performance issue long back, however couldn't recall entirely  how we resolved that.

vaguely remember our oracle date field had function based index, despite that  query performing full table scan per ' Oracle SQL tuning Adviser' report.

probably we took out function based index,   replaced cognos function with db functions and also went for different composite index.

as i have moved out of that work-group cannot get hold of code/doc to see what exactly we did.

good luck and let me us know how you resolved it at the end ( if possible)

Regards,
Vinu