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

[CWilt@xxxxxxxxxxxx]

> -----Original Message-----
> From: Joe Pluta [mailto:joepluta@xxxxxxxxxxxxxxxxx]
> Sent: Monday, July 26, 2004 5:42 PM
> To: 'Midrange Systems Technical Discussion'
> Subject: RE: Single record access really required (was RE: 
> Views and Indexes)
> 
> 
> > From: CWilt@xxxxxxxxxxxx
> > 
> > I'm simply showing the select here, not the code to create the
> > cursor or do the fetch.  Also, %ifnull(itm.item) is my pseudocode
> method
> > of
> > saying check the null indicator used for the ITEM field 
> returned from
> the
> > ITM file.
> 
> Well, let's see here.
> 
> First, you're basically doing all my code plus extra, and you haven't
> even shown the rest of the code.  Second, you're doing a select into,
> which we've found is much slower than the corresponding CHAIN.  Third,
> you don't show the code for the GetSysVal function.  Finally, you're
> reading records you may not have to read - in my version, if the IGL
> field is non-blank, no other records are read.

First off, I'm not doing a select into.  I'm opening a cursor and doing a
single row fetch.  Here's the point of the exercise:

"You don't say where ITEM and CUST are coming from.  I'll assume a file
named
INPUTFILE. "

Instead of five individual I/Os using READ (on INPUTFILE) and CHAIN, my
program only does one and lets the DB handle the others via the joins.

Of course I'm not showing all the code, you don't either.  The post was
intended to show the select used to build the cursor and the fact that the
application logic didn't change much.  The GetSysVal I'm using is the same
one you're using.  You didn't show the code for it but you're knocking me
for it?

You're correct that I'm reading more records than necessary.  That could be
dealt with simply by using the following select instead:
select A.*, itm.item, itm.iglovr, iic.icgl, cus.cucomp, com.dftglact
from INPUTFILE A 
        left join itm on A.item = itm.item
        left join iic on itm.iglovr = ' '
                            and itm.item = iic.item 
        left join cus on itm.iglovr = ' '
                              and A.cust = cus.cust
        left join com on itm.iglovr = ' '
                              and iic.icgl = '*COMPANY'
                              and cus.cucomp = com.comp
where
  <...>  

Downside to this is the added logic.  If most of the time the company level
default is used, then the original post is going to perform better.  On the
other hand, if most of the time the item override is used then this would
perform better.  One thing to keep in mind is that my method, only reads a
couple of extra fields.  Using native I/O you have to read the entire record
into the buffer just to look at one field.

Let me reiterate, the entire point to this post was to show that the single
record access you were doing wasn't required.

If you want a full fledge program for performance testing.  Then post the
DDS or DDL for the files and your full RPG program either here or on the new
site.  We'll put together a version that doesn't use native I/O and see what
happens.



Charles