RE: SQL vs. traditional I/O?

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

> 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