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

[CWilt@xxxxxxxxxxxx]

Joe,

You're still trying to use SQL to read one record at a time.  At least you
didn't do three separate select intos.

Where is rptr coming from?

Charles


> -----Original Message-----
> From: Joe Pluta [mailto:joepluta@xxxxxxxxxxxxxxxxx]
> Sent: Monday, July 26, 2004 7:14 PM
> To: 'Midrange Systems Technical Discussion'
> Subject: RE: Single record access really required (was RE: Views and
> Indexes)
> 
> 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
>