RE: SQL vs. traditional I/O?

["DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>]

Joe, for this, you might want to review the QAQQINI configuration for the
options that allow DB2 to process tasks in parallel.  There are also
optimization parms that might affect the performance of the SQL.

There's a really great page that gives some great tips on SQL optimization,
so it might be wort some time up front to get off to the right start....

http://www-1.ibm.com/servers/enable/site/bi/teraplex/lessons.html

I think this topis is great, and I hope to learn alot from this thread.  

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-898-7863 or ext. 1863



-----Original Message-----
From: Joe Pluta [mailto:joepluta@xxxxxxxxxxxxxxxxx]
Sent: Friday, July 23, 2004 12:35 PM
To: 'Midrange Systems Technical Discussion'
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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.