Interesting. Thanks for running this. On the last set of tests, what if
you changed the trigger to assume the row existed and try the update
directly. If the update doesn't find the row then insert. I think you'll
save yourself an IO.

-Walden

--
Walden H Leverich III
Tech Software
(516) 627-3800 x3051
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com

Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Wednesday, September 10, 2008 10:30 AM
To: RPG programming on the AS400 / iSeries; Midrange Systems Technical
Discussion
Subject: Interesting Benchmarks -- SQL, RPG, Journaling, and Triggers

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 thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].