× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.