Re: Interesting Benchmarks -- SQL, RPG, Journaling, and Triggers

Hi, Charles:

I think it would be even better if you could also post samples of your "benchmark" programs, and the SQL DDL used to create any files, e.g. by posting them at http://code.midrange.com -- and include a link to the posted source in an e-mail to the list. That way, we could examine the various programs, or modify them to run our own "benchmarks" etc.

(If your programs contain any "proprietary" information or use proprietary files etc., perhaps you could prepare "cut-down" versions that use only "sample" files and fields, etc.?)

Thanks,

Mark S. Waterbury

> Charles Wilt wrote:

All,

Thought these would be of interest to both lists....

The following benchmarks were run in batch on a 515 running v5r4.
- 4GB memory
- 4x4327 70GB DASD in a RAID-5 array
- processor feature 6021
- without SMP active

I was the only user.

FILE is a SQL DDL defined table with a primary key containing approx 2.9
million records. All updates done to a single field in every record.

Test CPU used Clock
Time
--------------------------------------------- ------------
------------
file not journaled
update using RPGLE 25
1:00
update using SQL 24
:47
File journaled, no commitment control used
update using RPGLE 64
13:25
update using SQL 65
13:19
File journaled, using commitment control
update using RPGLE (single commit) 61
7:44
update using SQL (single commit) 61
4:47
update using RPGLE (commit every 1000 recs) 41
1:58
File journaled, RPGLE commits every 1000 records
dummy RPGLE trigger, LR = *ON no files 260
7:30
dummy RPGLR trigger, LR = *ON 1 file 519
16:25
dummy RPGLE trigger, LR = *OFF no files 215
6:11
dummy RPGLE trigger, LR = *OFF 1 file 215
6:11
File journaled, RPGLE trigger that updates "shadow" table
using SQL, no commit 470
34:52
using SQL, single commit 493
23:49
using RPG, commit every 1000 344
11:02

Note the last set of tests, the trigger program was "real" and designed to
keep the "shadow" table in sync with the table being updated. The shadow
table contains a subset of teh real tables fields. So it first checks to
see if the record already exists in the shadow table, if so it is updated,
if not it is inserted. In the above tests, only updates were done to the
shadow table.

Hope somebody finds this useful.
Charles Wilt