Re: SQL Tools and Performance

[CRPence <CRPbottle@xxxxxxxxx>]

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>