Try to create an Encoded Vector Index with an include clause:
CREATE ENCODED VECTOR INDEX YourSchema.YourEVI
ON YourSchema/YourFile (Delivery) INCLUDE(Count(ItemNo),
Count(Distinct ItemNo)) ;
And rerun your query again.
Was the new EVI taken?
The access plans get saved and reused as well as the ODP can be reused, but
if a table scan must performed, all records are read each time the query is
executed (new rows between the calls could have been inserted or deleted)
Depending how big your table is, it may take some time
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!"
-----Ursprüngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Gary
Thompson
Gesendet: Friday, 04.7 2014 16:57
An: RPG programming on the IBM i (AS/400 and iSeries)
Betreff: Fastest way to get item count
I'm working on an SQLRPGLE V7R1 app to provide interactive error checks for
a heavily used 3rd party app.
This app uses a 3rd party user exit to check customer delivery documents.
One need is to get a count of items delivered and the times I see using Run
SQL Scripts for the following two sql, surprisingly, run over 2.4 seconds:
SELECT COUNT(DISTINCT item) AS items FROM lib1/file1 WHERE delivery = 123;
SELECT COUNT(item) AS items FROM lib1/file1 WHERE delivery = 123;
Visual Explain: 1-Table scan, 2-Aggregation, 3-Final Select
Total Estimated Run Time (ms) 3,356, CPU TIME 1
The following sql seems the better choice:
SELECT item FROM lib1/file1 WHERE delivery = 123 ORDER BY 1;
This last sql consistently runs < 11 ms
Visual Explain: 1-Table scan, 2-Temporary Sorted List, 3-Sorted List Scan,
4-Final Select
Total Estimated Run Time (ms) 3,346, CPU TIME 5,010
My question: Is it possible execution plans for the sql using COUNT don't
get saved ?
Stats from Visual Explain don't, for me, show the last sql as best performer
?
--
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:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
midrange.com
