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

On Wed, Sep 10, 2008 at 3:46 PM, Walden H. Leverich
<WaldenL@xxxxxxxxxxxxxxx> wrote:

Something's very wrong.

I find it hard to believe, but I'd accept it, that RPG beats out a
single sql statement, by 2:1 when there's a trigger, as shown in the
last set of tests. However, I simply cannot believe that SQL w/a cursor
beats out SQL w/out a cursor, again by 2:1. That makes no sense.

Actually it makes perfect sense. As I pointed out to Joe, the problem
is journalling. With both the RPG and SQL Cursor, I can use
commitment control and limit the number of records per transaction to
a reasonable number, in my test I used 1000.

Using SQL without a cursor, all 2.9 million updates are in the same
transaction. The sheer resources that sucks up is the problem.

But without commitment control, the system forces each and every
journal transaction to disk before going to the next. UNLESS you have
5722-ss1 opt 42 HA Journal Performance. Which provides caching or
journal entries without commitment control.

Also, I wonder if it would be different using a SQL trigger, especially
one registered as FOR EACH STATEMENT instead of the default FOR EACH
ROW.

That's a good question. I'd like to test it. I think the sheer
number of entries might still be an issue. Another thought which may
help is multithreading the SQL update without a cursor. There's an
article in this months System i News VIP section which talks about
that.

I've played with the multithreading, but my second and third jobs
ended abnormally with a record locked error.

I haven't figured out what I did wrong yet:
update addrintbl set phone = phone + 1
where akey in (select t1.akey
from addrintbl t1
join table (select akey,
row_number() over (order by akey)
as row_number
from addrintbl
) as NTE1 using (akey)
where row_number between :StartRow and :endRow
);

Charles