Re: Timestamp vs Date/time Decimal fields, Overhead in RPG programs...

On 17/03/2010, at 11:34 PM, <Rick.Chevalier@xxxxxxxxxxxxxxx> <Rick.Chevalier@xxxxxxxxxxxxxxx > wrote:

While doing some SQL vs. native I/O benchmarks a few years ago we discovered incidentally that adding the %date BIF to convert a numeric field containing a date value to a date data type added 20 minutes to our processing. The test consisted of running over a few million records (don't remember the exact count now) and performing some simple calculations. While many shops don't routinely run batch processes over that many records it is very routine for us. As a result when needing to convert dates in a process that will run over a very large data set we avoid using the %Date BIF. If it is a interactive process or a small set of records using the %Date BIF is ok.

And what impact did making your fake numeric database date fields real date fields have on your processing time? Doing so should remove the need to convert using %DATE within your program.

Also, what is the impact of using %DATE on current releases? You really cannot rely on benchmarks from "a few years ago" because things change. As chuck pointed out it seems that RPG/database now handles date types more efficiently than previously.

Finally, what percentage of the total run time was that 20 minutes? If the total run-time is 30 minutes and using %DATE pushes it out to 50 I concede you may want to address it but if the total time it 6 hours and %DATE makes it 6 1/2 hours then so what? If you have a limited window in which to complete the processing that 20 minutes might push you over the edge but still I suspect using real date fields would have reduce that impact and provided development benefits.

This sounds much like the old "external program calls are slow" issue. They were on early CPF but they got much better on later releases and a side effect of the introduction of ILE was to reduce the external CALL code path thus improving its performance. Additionally, new hardware is faster thus the "newer" or "more correct" but "slower" technique has less of an impact. Sure, if for example you're calling an external date conversion routine and you have to call it for 6 dates per record and you're processing a few million records then certainly the call impact will still be measurable but again that impact has to be weighed against the development/maintenance benefits and also against speed improvements on later releases.

Regards,
Simon Coulter.
--------------------------------------------------------------------
FlyByNight Software OS/400, i5/OS Technical Specialists

http://www.flybynight.com.au/
Phone: +61 2 6657 8251 Mobile: +61 0411 091 400 /"\
Fax: +61 2 6657 8251 \ /
X
ASCII Ribbon campaign against HTML E-Mail / \
--------------------------------------------------------------------