RE: Single record access really required (was RE: Views and Indexes)

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

> From: rob@xxxxxxxxx
> 
> Now, is that still slower than the three chains in your example?

What do you think?

I created a couple of new files, one with 100,000 records (to emulate
the customer master) and one with 100 (to emulate item class) and then
added a couple of foreign key fields to the first file.  Here's the
results:

c                   eval      mykey = %char(rptr)
c     mykey         chain     j4pp1r             
c     fk1           chain     j4pp2r             
c     fk2           chain     j4pt1r             

That's what it took to modify the native I/O program...

c                   eval      mykey = %char(rptr)   
c/EXEC SQL select a.key1, b.key1, c.key1, c.data1   
c+           into :akey1, :bkey1, :ckey1, :cdata1   
c+           from j4pp1 as a                        
c+           left join j4pp2 as b on a.fk1 = b.key1 
c+           left join j4pt1 as c on a.fk2 = c.key1 
c+           where a.key1 = :mykey                  
c/END-EXEC                                          

Ugh.  There's the SQL version.

Results?  100,000 iterations in native I/O took 22 seconds.  10,000
iterations in SQL took 11 seconds.  Native wins, 5-to-1.

Now, tell me again what the benefit is of SQL?  It's not shorter code,
it's not simpler syntax, it's not speed, it's not efficiency.  And with
embedded SQL it's not that silly red herring of platform independence.

So please.  Tell me.  Why would I use SQL here?

Joe