Impromptu queries run slow when i do numebr to date

[mickol]

I'm running an impromptu report which pulls information from different tables on our iSeries. everything's ok and the queries run really fast until i start to do anything with dates.
our system stores dates as numbers eg 20050908 and to find out if an order is overdue i have been using number to date function for expected date and then compare it with the functions either today or curdate.
the query goes from taking a few seconds to sometimes 5 to 10 minutes, there are only 18k of lines in the report.
i have other reports which are 300k+ but which run really quick (which don't contain any NTD or curtime functions)
it doesn't seem to matter whether i use curdate (which runs on the database) or today (which runs on my computer) they both slwo the report right down (

anyone have any ideas

thanks

 

[GJParker]

The Number-To-Date function is an impromptu function and can not be passed to the database for processing, so all the records will be brought from the server and filtering is performed on the local PC, Henec the slow down in performance.

If possible I would look to create a new table on the datbase which simply holds todays date in the correct number format, this can then be linked to your report for filtering.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[drlex]

Alternately, you could always convert your test date to a number and then use the apposite numerical comparator so that you are filtering on the numbers.
lex


soi la, soi carré

 

[griffindm]

I'd look at whether your database has an internal function that supports NYD, and if so I'd investigate either adding it to Impromptu via the database-specific .ini files, or just using it in a view of the data and then using the view in Impromptu rather than the original table.

Dave G.


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[mickol]

hi guys

thanks for the help, i seem to have got it running a lot faster now

 

[plevey]

Dave G is correct.
Use SUBSTR AND CONCAT to convert old DB2 numeric dates to a string representation of a date and then reCAST to a DATE type. That way your SQL can be fully resolved on the i-series.

If fishing was easy it would be called catching