Have you tried using parameterized sql?
Statement = 'SELECT COUNT(DISTINCT ITEM) AS ITESM FROM LIB1/FILE1 WHERE
DELIVERY = ?';
Creating a program or sub-procedure specifically to return count. Allow
the program to keep LR off.
Dcl-DS InCountDS Qualified;
Key Packed(9);
End-Ds;
Dcl-s OutCount Int(10);
Dcl-s OutCountNl Int(5);
In the Inzsr (or the first time the subprocedure is triggered) Run the
Prepare and Declare.
Statement = 'SELECT COUNT(DISTINCT ITEM) FROM LIB1/FILE1 WHERE DELIVERY
= ?';
PREPARE
DELCARE
At run time Open using the new key, fetch, then close. The system
doesn't have to re-plan the sql ever time its run.
InCountDs.Key = InputValue;
OPEN USING :InCountds
Fetch into :OutCount :OutCountNl
If sqlstate <> '00000';
Clear outcount;
Endif;
Close
If OutCountNl = UDFSQL_PARM_NOTNULL;
Return OutCount;
Else;
Return 0;
I've found this method to be very efficient when I'm calling the same
count statement multiple times with different key values.
I have also used this approach to put the entire count sql into a
subprocedure with static fields or Global fields for the sql statement.
The first time you call the subprocedure you need to prepare and
declare. The rest of the time you just need to open, fetch, and close.
Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the
author and do not necessarily represent those of the company.
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Gary
Thompson
Sent: Friday, July 04, 2014 8:57 AM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: 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
