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.
midrange.com
