Re: Fastest way to get item count -- RPG400-L

EVIs do have statistics - so do PFs - but we cannot use them in SQL statements that I know of.

Some make a big deal of statistics in EVIs - yes, they are there - AND statistics are available for any PF - you just need to say that you want them.

And count is part of a PF - just look at DSPFD - but you can't find a "field" in a PF that holds it - that we can use.

HTH
Vern

On 7/4/2014 2:54 PM, Raul A Jager W wrote:

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