Re: Faster key lookup

[Jonathan Ball <jonball52@xxxxxxxxx>]

I meant to add the first time...

Don't forget to initialize the RC host variable to zero if this step 
might run more than once in a job.

On 6/28/2018 2:03 PM, Justin Taylor wrote:
> That's on my list to try. Thank you
>
>
> ________________________________________
> From: Jonathan Ball [jonball52@xxxxxxxxx]
> Sent: Thursday, June 28, 2018 12:07 PM
> To: rpg400-l@xxxxxxxxxxxx
> Subject: Re: Faster key lookup
>
> Some pretty good alternatives offered by others.  Here is mine.  It
> appears you don't really care about the exact number of rows in the
> table that match the WHERE predicate.  All you want to know is if it is
> zero or not zero, i.e.  "at least one".
>
> With that in mind, change the query as follows:
>
>     EXEC SQL
>       Select 1
>         Into :rc
>         From MY_TABLE
>         Where COLUMN1 = :local1 and COLUMN2 = :local2 and COLUMN3 = :local3
>                And (COLUMN4 = :local4 or :local4 = '')
>       Fetch first 1 row only
>     Return (SQLSTATE = '00000' and rc > 0);
>
> There should be an index with the four COLUMNx columns in place.
>
> On 6/27/2018 5:30 PM, Justin Taylor wrote:
> > I have a *SRVPGM that calls a procedure a lot, and I need to speed it up.
> >
> > Here's the entire code for the procedure:
> > EXEC SQL
> >     Select count(*)
> >       Into :rc
> >       From MY_TABLE
> >       Where COLUMN1 = :local1 and COLUMN2 = :local2 and COLUMN3 = :local3
> >              And (COLUMN4 = :local4 or :local4 = '');
> > Return (SQLSTATE = '00000' and rc > 0);
> >
> > MY_TABLE is reloaded once a day and is otherwise static.  It has 9K rows that are 24 bytes long (it only has the 4 columns).
> >
> > I know I could do a CHAIN and gain a little bit, but I'm hoping for more.  My thought is to on the first call, read the entire table into a sorted array and have the procedure do a couple of %lookup()'s.  Is there a better way?
> >
> > TIA