RE: SQL vs. traditional I/O?

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

IIRC, compile options play a role in embedded SQL programs.  
Take a look at ALWCPYDTA(*OPTIMIZE), CLOSQLCSR(*ENDACTGRP), COMMIT(*NONE),
ALWBLK(*ALLREAD).  There may be others.
Also, QAQQINI file may have some options to enforce caching.


-----Original Message-----
Subject: RE: SQL vs. traditional I/O?

> From: Joe Pluta
> 
> I put in a semi-randomizer (I bump the counter a couple of thousand
> records between reads), and got even more interesting results.  The
> first time through, the RPG performs worse than the SQL by a factor of
> about 3.7.  Run again (thus reading the same records), the RPG program
> improves by a factor of nearly 40, beating the SQL 10 to 1.  The SQL
> program does not show significant improvement between successive
trials.

One more result, and THIS is a stunner, folks.

I put in some more randomization - I allowed a starting record, so the
program could work through multiple places in the file.  I then
submitted 12 jobs simultaneously to read 25,000 records.   Results:

SQL took nearly three minutes (176 seconds) to complete all 12 jobs.
The CPU was maxed the whole time.

The first run of RPG took only 26 seconds.  All twelve jobs finished
faster than running ONE pass.  The second run took 20 seconds.  And now,
no matter where I set the seed value, the RPG version is taking only
seven to eight seconds for 100,000 records.

This is obviously a caching issue, but what's so cool is that with
native I/O, caching from ONE job was obviously improving the performance
of the OTHER jobs.  And the caching was cumulative as you added more
jobs.  No such luck for SQL.

Okay, enough.  Things to do, people to see.

Joe