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

Interesting Benchmark. I've always heard people say that when doing
massive IO operation, SQL perform better. Your result doesn't seems to
support that (base on CPU time). Especially, RPG is doing extract work
of reading the data into the program first and then update, which, in
real world, most applications would need to read the data first, perform
business logic, and then update. Massive SQL update statement without
fetch first rarely happen in most applications.

This is probably a Barbara question. I wonder if there is performance
different between update with and without %fields()?

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

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.