Re: SQL Tools and Performance

[CRPence <CRPbottle@xxxxxxxxx>]

On 12-Mar-2015 11:32 -0500, Gerald Kern wrote:
> <<SNIP>> putting the B and pressing enter displays the bottom of the
> result set *IMMED.

  If going to the bottom of a 600K rows result-set within the report 
writer of the Start Interactive SQL (STRSQL) feature is effected 
immediately, that implies the Data Refresh (REFRESH) parameter 
specification was the special value *ALWAYS.  Use F13=Services either to 
modify the /Data Refresh/ session attribute from the value of *ALWAYS to 
the value of *FORWARD to reflect the effect of actually fetching and 
formatting all of those 600K rows or to modify the SELECT Output session 
attribute to be 2=Printer.  Using the *ALWAYS data refresh results in 
[mostly] only those rows being displayed being fetched and thus\then 
formatted for presentation in the /report/ window [aka the Display Data 
panel].

> <<SNIP>>
> It is a quite simple statement that I used in STRSQL and in the PC
> tools:
>
>   select STATUS,ECWUID,CHART
>   ,ECWACCT,LASTNAME,FIRSTNAME
>   ,BIRTHDATE,RPPTSX
>   from ecppatxrf
>   join rpmast
>    on chart = rpptno
>   where rpstat in ('A', 'I')
>     and rploc = '0'
>   order by ecwacct

  Note: The use of *unqualified* names in the table-references implies 
the possibility that the same data is not being accessed in both query 
requests.  Best to library-qualify the names to ensure the requests are 
indeed identical.

  To get a more appropriate and accurate comparison of that query when 
requested within STRSQL using output to the display versus that same 
query requested from the PC client software, add the following clause to 
the end of the query when requested from the PC client:
      FETCH FIRST 20 ROWS ONLY

> I don't think it's a memory issue (64 bit Win 7 with 16 gb ram), but
> I suppose the PC tools could have their own constraints. Network
> isn't the issue as we do a lot of image distribution/exchanges with
> hospitals and have Gigabit fiber running internally in our network.
>
> The job eventually did finish after about 20 minutes.

  Eliminate from the equation those potentialities of memory and 
network effects from use of the PC\client interface, by requesting the 
following revised query [encapsulated in a CREATE TABLE] on both the 
server-based client (STRSQL) and whichever PC client sfw, and compare 
[timings of] those; environmental issues such as the WM and the Sort 
Sequence (SRTSEQ) may still expose differences, but the relevance of the 
client and the network are diminished essentially to the point of 
irrelevance:

   create table qtemp/alloutput as
   (select STATUS,ECWUID,CHART
    ,ECWACCT,LASTNAME,FIRSTNAME
    ,BIRTHDATE,RPPTSX
    from WhichLibA . ecppatxrf /* rpl WhichLibA accordingly */
    join WhichLibB . rpmast    /* rpl WhichLibB accordingly */
     on chart = rpptno
    where rpstat in ('A', 'I')
      and rploc = '0'
    order by ecwacct
   ) with data

  While arguably that does not _solve_ anything, at least what can be 
revealed is quantifying what the client can achieve in performance for 
completing the query _without including_ what work\memory\network is 
required *additionally* to obtain that result set at the client PC; the 
excess work that is included, the creation and population of the TABLE 
with row-data, should be of an equivalent cost in both requests and thus 
is probably not so significant as to skew the interpretation of the 
results with regard to presuming what can be achieved.