RE: SQL vs. traditional I/O?

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Joe, it's not really caching, it's just the result of data being in memory 
that is available to all processes. Unit testing would need you to CLRPOOL 
and SETOBJACC *PURGE in order to compare apples to apples. Another kind of 
testing would be what you are doing now, to show what happens when you 
leave things in memory.

Vern

At 12:34 PM 7/23/2004, you wrote:
> > 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