Re: Faster key lookup

I think that creating four arrays would be a good approach.

I've used this technique to speed up this kind of app on many occasions.

I have also loaded it into a User Space which has the added advantage that a) The load can be triggered by the daily table reload process and b) It can be shared by multiple users. So you just resolve the user space pointer once on first load of any program.


Jon Paris

www.partner400.com
www.SystemiDeveloper.com

On Jun 28, 2018, at 1:30 AM, Justin Taylor <JUSTIN@xxxxxxxxxxxxx> 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
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: http://amzn.to/2dEadiD