I'll definitely create an EVI. It should be a low-hanging fruit.
The table is build with some complex logic, so an MQT wouldn't be easy to load with a SELECT.
-----Original Message-----
From: Birgitta Hauser [mailto:Hauser@xxxxxxxxxxxxxxx]
Sent: Thursday, June 28, 2018 12:29 AM
To: 'RPG programming on the IBM i (AS/400 and iSeries)' <rpg400-l@xxxxxxxxxxxx>
Subject: RE: Faster key lookup
I won't than loading everything into an array an than using %LOOKUP will be faster than SQL with the right indexes.
You should create an EVI (Encoded Vector Index) with an INCLUDE clause that automatically updates aggregate information as soon as a row is inserted, updated or deleted.
Create Encoded Vector Index YourSchema.YOUREVI
On YourSchema.YourTable(Column1 Asc, Column2 Asc, Column3 Asc, Column4
Asc)
Include(Count(*))
With this INCLUDE clause an EOA (Encoded Vector Index Only Access) can be performed, i.e. the information is read directly from the EVI (i.e. from the system table). When executing the query there is no need to access any database record.
If you consider to load your table once a day, you may also create an MQT (Materialized Query Table) which gets refreshed once a day.
Create Table YOURSCHEMA.YOURMQT
As (Select COLUMN1, COLUMN2, COLUMN3,
COLUMN4, COUNT(*) As TOTAL
From YOURTABLE
Group By COLUMN1, COLUMN2, COLUMN3,
COLUMN4)
Data Initially Immediate
Refresh Deferred
Maintained By User
Enable Query Optimization;
For refreshing the MQT use the Refresh SQL Command:
Refresh Table YOURMQT;
You either can access the MQT directly (with SQL) or you need to allow the optimizer to use the MQT (like an Index) by setting the MATERIALIZED_QUERY_TABLE_USAGE and MATERIALIZED_QUERY_TABLE_REFRESH_AGE
options in the QAQQINI File.
With an additional index over the MQT with the same key fields (Column1, Column2, Column3, Column4) the access could be even faster.
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
