You really need to count the rows, or do you only want to check whether
there is at least on row that fits the where condition?
If so, the following SQL Statement should perform faster:
Clear RC;
Exec SQL Select 1 into RC
From YourFile
Where Column1 = :Loc1 ...
Fetch First Row only;
Return (SQLCODE >= 0 and SQLCODE <> 100 and RC > *Zeros);
In either way you need an index over all the columns you specified in the
WHERE Conditions
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxx> On Behalf Of Justin Taylor
Sent: Donnerstag, 28. Juni 2018 02:31
To: RPG programming on the IBM i (AS/400 and iSeries)
<rpg400-l@xxxxxxxxxxxx>
Subject: Faster key lookup
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
midrange.com
