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

Charles Wilt wrote:

Joe,

The real problem is journaling. 2.9 million records in a single commit
transaction is probably a bit much<grin>.

Particularly if you take into account the fact that the shadow table
is journaled and updated under the same commit transaction.

When commit is not used, you force the system to wait till the journal
entries get written to disk before the next record is updated. When
commit is used, the system will cache the journal entries till it gets
a page full or COMMIT is issued.

With RPG, I can easily perform a commit every 1000 records. I could
do the same using an SQL cursor, but from the tests you and I have
done in the pass I know SQL with a cursor is slower than RPG RLA.

If I had 5722-SS1 option 42, HA Journal Performance. Then the system
would cache the journal entires even if I wasn't using commitment
control. In that case, I'd expect SQL to beat RPG by 25-30% as it
does if the file is not journaled.

Charles


On Wed, Sep 10, 2008 at 1:02 PM, Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx> wrote:

Charles Wilt wrote:

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"

Wow. I'd have expected the SQL update process to WAY outperform the RPG
in this case. Updating 2.9 million records without having to "pass
through the MI layer" - that is, the SQL engine should have done all the
work without coming back to the HLL program - as opposed to the 2.9
million round trips for the RPG program should have resulted in a clear
win for SQL. Maybe the clock time savings reflected it, but even so I'd
have expected a lot more benefit for SQL.

Maybe they've increased the performance of RPG I/O as well, and aren't
telling us <grin>.

Actually, I was talking about the uncommmitted tests. Even that one, the CPU time was nearly constant and the clock time was less than 25% savings. As Lim points out, everything we've been told about how SQL outperforms RPG on large sets isn't really borne out here. Even a 25% decrease in clock time, while significant, isn't nearly as much as I'd have expected - especially when the CPU time is barely lower.

Joe