Re: Performance question about "next number" methods

Not going to do any benchmarks for you, Mark .-) but my gut and "experience" says that the data area has to be faster.


But how much faster? It all looks rather esoteric. That is, while option #2 may be, say, 100 milliseconds slower (just a number for comparison, not a fact), why would it matter? Volume is the only thing that comes to my mind. If I am updating the NextNumber for, say, invoicing in our shop (1000 per day and on an as needed basis), no one is ever going to notice. On the other hand, if I'm rolling over a 1,000,000 record or larger file in the middle of the day, it might be problematic (a night batch job - here, anyway - wouldn't be). But then, if I was doing a massive update like that, I probably (depending upon the total picture, you understand) would read the file or data area on first cycle and not update it until end-of-file; conserve disk I/O where possible.


Why do you ask, anyway? Besides the basic academic question, I mean.


* Jerry C. Adams
*IBM System i Programmer/Analyst
B&W Wholesale Distributors, Inc.* *
voice
615.995.7024
fax
615.995.1201
email
jerry@xxxxxxxxxxxxxxx <mailto:jerry@xxxxxxxxxxxxxxx>



Mark S. Waterbury wrote:

Can anyone comment on the performance of the following alternative "next number" techniques:

1) increment a value stored in a data area (e.g. in RPG, something like this):

*LOCK IN DATAAREA
ADD 1 NEXTNBR
OUT DATAAREA

2) store a record in a keyed "next numbers" file that stores the current value for each file
(where the "key" is the name of the file that this next number pertains to)

pseudo-code below:
read record with key, with a record lock
increment next number value
update record, releasing the record lock
return new value to caller
3) using newer features in DB2 UDB, e.g. a sequence or identity field

These techniques could be implemented in any ILE language, RPG IV, COBOL, C, etc.

I am interested in the relative performance of one technique versus another.

Thanks.

Mark S. Waterbury