× 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.



Hmm, as in DB2 auto-magic creates the EVI ?
However my response times from RUN SQL did seem slow . . .
so I'm wanting to test the EVI.

As Birgitta anticipated, the base file is very heavily updated
so io would be an issue, and my app is to run on one of those
file update points.

Thanks and a happy week's end to all on the list - I'm the better
for your support !

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Raul A Jager W
Sent: Friday, July 04, 2014 1:55 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: Fastest way to get item count

I belived that "count" was included by default in EVI indexes

On 07/04/2014 11:57 AM, Gary Thompson wrote:
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.


-- Este e-mail fue enviado desde el Mail Server del diario ABC Color --
-- Verificado por Anti-Virus Corporativo Symantec --
--
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 ...

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.