Re: SQL Tools and Performance

On 12-Mar-2015 08:34 -0500, Vernon Hamberg wrote:

On 3/12/2015 8:18 AM, Gerald Kern wrote:

<<SNIP>>
I have a relatively simple SQL statement, that when run on a green
screen via STRSQL takes less than 3 seconds to complete. The result
set contains about 600,000 records.

However, if I use the Database Development perspective in RDi, or
the SQL Explorer plugin in Rdi or the standalone product AQT
(advanced query too, which supports DB2 for i), the same SQL
statement takes considerably longer and by considerably I mean
upwards of 15 minutes - if it does complete at all. <<SNIP>>

STRSQL makes an assumption that it will return the first page of
records quickly - "batch" type tools make the assumption that the
entire set has to be returned before displaying anything.

Any client tooling that provides a report writer that offers what is effectively output to the display could [and IMO should] implicitly operate with an optimization that assumes the first page of data should be returned quickly. The query requester has the option to explicitly code the OPTIMIZE FOR N ROWS clause on any SELECT statement, and that should override whatever the client feature otherwise would do implicitly; that is true for the server-based 5250 client performing the SQL request from within the STRSQL statement processor with report writer.

In STRSQL, do you mean by "complete", that you see something on the
screen? What happens when you put B in the control field and press
Enter? That will make it go to the bottom, hence going through the
entire result set. <<SNIP>>

The B [or *BOT] positioning request will only FETCH the data when the /Data-Refresh/ option is set to *FORWARD. For /live data/ queries [i.e. the Data-Refresh is set to *ALWAYS], the effect of the request has the report writer simply discarding the results of the FETCH FIRST [and any required FETCH NEXT to fill the first screen], then the feature effectively positions to the end with a FETCH LAST then does a FETCH PRIOR from the /scrollable cursor/. Without *FORWARD, the results for a *BOT positioning could appear rather quickly despite possibly a very large result set, whereas with *FORWARD the report writer not only fetches but also builds the report output for every fetched row onward to the last row.

For the archives, I responded to the cross-posted OP here:
<http://archive.midrange.com/wdsci-l/201503/msg00102.html>