× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.