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

On the RPG trigger,
It does a chain, and if %found() update else write. As I mentioned, all the
records were in the shadow table so they were always %found() for my tests.

The RPG update process read the file in arrival seq, and updated. %fields
was _not_ used as the READ and UPDATE was done directly to a data
structure. Thus, %fields would have no effect and probably isn't even
valid.

The SQL update process did an "update myfile set myfield = myField + 1"


Charles

On Wed, Sep 10, 2008 at 11:17 AM, Lim Hock-Chai <
Lim.Hock-Chai@xxxxxxxxxxxxxxx> wrote:

Thanks for the stat. Got a couple questions below:

On your sql update, do you do "fetch, change the value, and then update"
or do you just a "update myFile set myField='AAA'"?

On your RPG update, I'm assuming you do "read, change value, update
using %field", correct?




"Charles Wilt" <charles.wilt@xxxxxxxxx> wrote in message
news:<mailman.9810.1221057094.2545.rpg400-l@xxxxxxxxxxxx>...

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

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.