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



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 ?







As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

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.