hey folks,
I'm new to Cognos and I've run into a performance issue with a query that pulls data based on a numeric date.
The file is part of a 3rd Party Vendor package and the date is stored as a numeric in the YYYYMMDD format.
If I hard-code the date the results return in about 30 seconds.
If I convert the current date to a numeric using cast(replace(char(current date, iso),'-','')as int), it takes over 30 minutes. (Actually I don't know that it ever returns since I cancel at that point.)
My thought is to 'somehow' have Cognos produce the numeric date and pass it into the SQL. However I've not found a way to do that.
All ideas and suggestions are welcome.
Thanks
Oh how I feel your pain ....
Give this a try:
( year(current_date) * 10000 ) +
( month(current_date) * 100 ) +
day(current_date)
Unfortunately that didn't speed up the query any.
I did find the macro timestampmask and I'm trying to figure out how to incorporate it into the SQL where clause, assuming I can that is. :)
I mis-read your post initially. What I suggested would probably be about the same as what you were already doing. Assuming you have an index on the number column you'd want your filter criteria to be a number which is what you were doing. So I don't see why it would be slow compared to a hard coded number.
What is your filter expression? Presumably it is a detail filter set to "Before Auto Aggregation"?
You could also look at the SQL being generated for your query to see if that helps you understand what might be going on.