|
Birgitta,
Thank you.
The table for this app is very heavily used and has scheduled re-orgs
moving old trans to history.
As a personal "moon-shot", I'll try your EVI suggestion : )
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
Birgitta Hauser
Sent: Friday, July 04, 2014 9:47 AM
To: 'RPG programming on the IBM i (AS/400 and iSeries)'
Subject: AW: Fastest way to get item count
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.
--
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
