RE: SQL vs. traditional I/O? -- MIDRANGE-L

Joe,

You're doing row-at-a-time processing, you're just using SQL to do it.
Try this instead:

Add a "Order Total" field to your 800K row table. Then write an RPG
program that reads the rows and sums the order total. Try that against
"select sum(OrderTotal) from file" and see which is faster.

The fetch operation, while important, is in SQL so SQL can act like
classic IO. It's not what SQL is good at.

-Walden 


------------
Walden H Leverich III
President & CEO
Tech Software
(516) 627-3800 x11
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com 

Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)
 
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Joe Pluta
Sent: Thursday, July 22, 2004 9:10 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL vs. traditional I/O?

> From: rob@xxxxxxxxx
> 
> As I said before, a dyed-in-the-wool traditionalist got better
performance
> out of his SQL (he decided that it wasn't fast enough to justify
changing
> his programming paradigm).

I don't know who this person is, but I've just run a test for the fun of
it.  I realize this test is blisteringly unscientific, but the results
indicate that perhaps the hype might not match the reality.

I have a file with 800,000+ records, each with a 10-character key with a
different unique number (yeah, I know, numeric values in alpha field,
but hey, I just whipped it together).

I then took the following code:

c                   for       x = 1 to count    
c                   eval      mykey = %char(x)  
c     mykey         chain     j4pp1r            
c                   endfor                      

- and -

c                   for       x = 1 to count                            
c                   eval      mykey = %char(count)                      
c/EXEC SQL declare c cursor for select * from j4pp1 where key1 = :mykey 
c/END-EXEC                                                              
c/EXEC SQL open c                                                       
c/END-EXEC                                                              
c/EXEC SQL fetch from c into :j4pp1                                     
c/END-EXEC                                                              
c/EXEC SQL close c                                                      
c/END-EXEC                                                              
c                   enddo                                               

I compiled the two, and then showed the results.  I had to crank up the
native I/O to 100,000 iterations to get a measurable difference, while
the SQL version only required 10,000.

Results:

            Start  End    Count
Native I/O: 193459 193509 100000  
SQLRPGLE:   194230 194241 10000   

To the naked eye, in this very unscientific test, native I/O performed
roughly 10,000 chains per second, while SQL scored about 900 fetches per
second.

This is an order of magnitude.  This was with no optimization, right out
of the box.  I later optimized both programs, and the native I/O went
down to seven seconds, while the SQL program, with COMMIT(*NONE) and a
CHGPGM OPTIMIZE(*YES) dropped to ten seconds (for one tenth the records
of the native I/O).  So it seems optimization is even more effective for
native I/O.

I may have screwed up something here.  Feel free to point out any
issues.  But this is the sort of test I think the new IAAI site is going
to be very useful for.  And those who are interested should definitely
stop by the website.

http://forums.plutabrothers.com/IAAI

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.