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


  1.  Home
  2. MIDRANGE-L
  3. March 2015

Re: row_number performing badly in table with a lot of rows

Here is some code that illustrates the problem. It is a dspf and rpg
program. It measures the time it takes to write a page to the subfile.
Use F8 to add more rows to the table. watch as the more rows in the table
the slower the paging performance.

A DSPSIZ(24 80 *DS3)
A R SFL3526 SFL
A HDINDS 4A H
A HDSEQN 9 0H
A HDTEXT 256A H
A SFSEQN 9A B 8 2
A SFTEXT 60A B 8 13CHECK(LC)
A R CTL3526 SFLCTL(SFL3526)
A OVERLAY
A PRINT
A SFLCSRRRN(&RRN10)
A 31 SFLDSP
A N35 SFLDSPCTL
A 35 SFLCLR
A N32 SFLEND(*MORE)
A SFLSIZ(0014)
A SFLPAG(0014)
A 38 ROLLDOWN(37)
A 32 ROLLUP(36)
A RRN10 5S 0H
A 7 2'Seqnbr'
A DSPATR(UL HI)
A 7 13'Line text '
A DSPATR(UL HI)
A R HDR3526
A CF03(03 'EXIT')
A CF05(05 'refresh')
A CF07(07 'Position')
A CF08(08 'Add more')
A CF09(09 'REFILE')
A CF12(03)
A CF21(21 'COMMAND LINE')
A OVERLAY
A PRINT
A 1 2'DWH3526R'
A 1 62SYSNAME
A 1 72DATE
A EDTCDE(Y)
A 2 2USER
A 2 72TIME
A
A TITLE 35A O 1 23DSPATR(HI)
A 4 2'Elapsed time . . .'
A DSPATR(HI)
A SCELAPSEC 10A 4 21
A 5 2'Put/Get count . .'
A DSPATR(HI)
A SCPUTGETCX 10A O 5 21
A 4 40'Put/get average . . . .'
A DSPATR(HI)
A SCELAPAVG 10A O 4 65
A 5 40'Number rows in table . .'
A DSPATR(HI)
A SCROWCX 10A O 5 65
A R FTR3526
A OVERLAY
A 23 2'F3-Exit'
A COLOR(BLU)
A 23 12'F5-Restart'
A COLOR(BLU)
A 23 24'F7-Position'
A COLOR(BLU)
A 23 38'F8-Add more'
A COLOR(BLU)
A 23 52'F9-Refill table'
A COLOR(BLU)
** PROMPT TO POSITION AT SEQNBR.
A R POS3526
A CF03(03 'EXIT')
A CF12(03 'EXIT')
A BLINK
A OVERLAY
A WINDOW(4 7 6 44)
A CLRL(*NO)
A 3 2'POSITION TO SEQNBR . -
A . .'
A DSPATR(HI)
A SCSEQNBR 9 0B 3 28EDTCDE(Z)
A 5 2'F12=CANCEL'
A COLOR(BLU)

** PROMPT TO add more rows to file.
A R MORE3526
A CF03(03 'EXIT')
A CF12(03 'EXIT')
A BLINK
A OVERLAY
A WINDOW(4 7 6 44)
A CLRL(*NO)
A 1 6'Add More Rows To Table'
A DSPATR(HI)
A 3 2'Number of rows to add -
A . .'
A DSPATR(HI)
A SCNUMROWS 7 0B 3 28EDTCDE(Z)
A 5 2'F12=CANCEL'
A COLOR(BLU)


** dsh3526R - performance tester.
h option(*srcstmt:*nodebugio)
Fdsh3526d CF E WORKSTN SFILE(sfl3526:RRN10)
f infds(fdbk)
d @template s *
** --------------------- pr_quscmdln ---------------------------------
d pr_qusCmdLn pr extpgm('QUSCMDLN')
** ---------------------- infds data struct -----------------------
drefInfds ds qualified based(@RefStruct)
d fill1 80a
d odpType 2a
d FileName 10a
d LibName 10a
d splfName 10a
d splfLib 10a
d SplfNbr 5i 0
d aidByte 369 369a
d csrRow 3i 0
d csrCol 3i 0
d fill2 256a
** -------------------------- refSflRowKey --------------------------
d refSflRowKey ds qualified based(@template)
d seqnbr 9p 0
** ---------------------- refPerformanceStats ----------------------
d refPerformanceStats...
d ds qualified based(@template)
d bgnTs z
d endTs z
d putGetCx 5p 0
d elapSecs 7p 3
d rowCx 10i 0
** ------------------------ refProcessRv ----------------------------
** process entkey procedure return values
d refProcessRv ds qualified based(@template)
d topKey likeds(refSflRowKey)
d nxTopKey likeds(refSflRowKey)
d chgd n
** -------------------------- refSubfileCb --------------------------
** subfile state info. Info that persists from one put/get cycle to
** another.
** csrLineRrn : subfile line on which the cursor was located when
** display was read.
d refSubfileCb ds qualified based(@template)
d viewNum 10i 0
d pagSx 10i 0
** ------------------------ refFillSflReturn ------------------------
d refFillSflReturn...
d ds qualified based(@template)
d topKey likeds(refSflRowKey)
d nxTopKey likeds(refSflRowKey)
d sflCx 10i 0
** --------------------- display_ProcessEntKey ---------------------
ddisplay_ProcessEntKey...
d pr likeds(refProcessRv)
d extProc('display_ProcessEntKey')
d inSubfileCb likeds(refSubfileCb)
d inEntkey 10a const varying
d inTopKey likeds(refSflRowKey)
d inNxTopKey likeds(refSflRowKey)
d inCsrLineRrn 10i 0 const
d inStats likeds(refPerformanceStats)
** ------------------------- display_PutGet -------------------------
** write and read the display record formats.
ddisplay_PutGet pr 10a varying
d extProc('display_PutGet')
d outCsrLineRrn 10i 0
d inSubfileCb likeds(refSubfileCb)
** ------------------------ dsh3526p_PageDown ------------------------
ddsh3526p_PageDown...
d pr likeds(refSflRowKey)
d extProc('dsh3526p_PageDown')
d inTopKey const likeds(refSflRowKey)
d inSflPagSx 10i 0 const
** ------------------------- dsh3526p_PageUp -------------------------
ddsh3526p_PageUp pr likeds(refSflRowKey)
d extProc('dsh3526p_PageUp')
d inTopKey const likeds(refSflRowKey)
d inSflPagSx 10i 0 const
** ------------------------ dsh3526p_Ensure ------------------------
** make certain the dsh3526p table exists.
ddsh3526p_Ensure pr
d extProc('dsh3526p_Ensure')
** ---------------------- dsh3526p_GetLastRow ----------------------
ddsh3526p_GetLastRow...
d pr likeds(refSflRowKey)
d extProc('dsh3526p_GetLastRow')
** --------------------- dsh3526p_CreateMoreRows ---------------------
ddshpf85_CreateMoreRows...
d pr
d
extProc('dsh3526p_CreateMoreRows')
d inNbrRows 10i 0 const
** ------------------------- dsh3526p_Clear -------------------------
ddsh3526p_Clear pr
d extProc('dsh3526p_Clear')
** ---------------------- dsh3526r ------------------------------
** dsh3526R - report defn editor.
ddsh3526r pr extpgm('dsh3526R')
** ---------------------- FillDsplyHdr --------------------------
dFillDsplyHdr pr
d extProc('FillDsplyHdr')
d inSubfileCb const likeds(refSubfileCb)
d inStats likeds(refPerformanceStats)
** ------------------------ subfile_WritePage ------------------------
** Write a page of rows from dsh3526p to the subfile.
dsubfile_WritePage...
d pr likeds(refFillSflReturn)
d extProc('subfile_WritePage')
d inTopKey const likeds(refSflRowKey)
d inSubfileCb likeds(refSubfileCb)
** ------------------------ initSfl -----------------------------
dinitSfl pr
d extProc('initSfl')
d inViewNum 10i 0 const
** -------------------- performanceStats_Update --------------------
dperformanceStats_Update...
d pr
d extProc('performanceStats_-
d Update')
d inStats likeds(refPerformanceStats)
** ---------------------- sflRowKey_Construct ----------------------
dsflRowKey_Construct...
d pr likeds(refSflRowKey)
d extProc('sflRowKey_Construct')
d inSeqnbr 9p 0 const
** ------------------------- subfile_PosCsr -------------------------
dsubfile_PosCsr pr
d extProc('subfile_PosCsr')
d inSflCx 10i 0 const
** ------------------ subfile_ProcessLineChanges ------------------
dsubfile_ProcessLineChanges...
d pr
d extProc('subfile_-
d ProcessLineChanges')
d inSubfileCb likeds(refSubfileCb)
d inChgCx 10i 0
d inOutTopKey likeds(refSflRowKey)
** ---------------------- sub_IndGetFrom -------------------------
** get string of consecutive indicator values from *IN array.
dsub_IndGetFrom pr 99a varying
d extproc('sub_IndGetFrom')
d inInd n dim(99)
d inIx 10i 0 const
d inLx 10i 0 const
** ----------------- sub_IndToFuncKeyName ---------------------
** convert function key indicator to func key name
dsub_IndToFuncKeyName...
d pr 10a varying
d extproc('sub_IndToFuncKeyName')
d inInd 1n const dim(99)
d inPageDownInd 10i 0 const options(*omit:*nopass)
d inPageUpInd 10i 0 const options(*omit:*nopass)
** ---------------------- dsh3526r ------------------------------
** dsh3526R - report defn editor.
ddsh3526r pi
d fdbk ds likeds(refInfds)
DPAG10 S 4S 0
DRRN10 S 5S 0
d sflcx s 10i 0
d topkey ds likeds(refSflRowKey)
d nxTopKey ds likeds(refSflRowKey)
d csrLineRrn s 10i 0
d selSeqnbr s 9p 0
d processRv ds likeds(refProcessRv)
DSeqnbr S 9p 0
DRRN10TOT S 5S 0
d botSscc s 20a
d botLoc s 10a
d pagCx s 10i 0
d fillRv ds likeds(refFillSflReturn)
d chgCx s 10i 0
d cycleChgCx s 10i 0
d entkey s 10a varying
d sflCb ds likeds(refSubfileCb)
d fChgd s n
d stats ds likeds(refPerformanceStats)
/free
dsh3526p_Ensure( ) ;
dsh3526p_CreateMoreRows( 5000 ) ;
clear stats ;
clear topKey ;
clear nxTopKey ;
clear sflCb ;
selSeqnbr = 0 ;
sflCb.pagSx = 14 ;
sflCb.viewNum = 1 ;
chgCx = 0 ;
// DISPLAY SUBFILE UNTIL IND03 IS ON
dow 1 = 1 ;
initSfl( sflcb.viewNum ) ;
FillDsplyHdr( sflCb: stats ) ;
// write the subfile page.
fillRv = subfile_WritePage( topkey: sflCb ) ;
topKey = fillRv.topKey ;
nxTopKey = fillRv.nxTopKey ;
sflCx = fillRv.sflCx ;
// condition page down and page up.
*in38 = '1' ;
*in32 = '0' ;
if nxTopKey.seqnbr > 0 ;
*in32 = '1' ;
endif ;
rrn10 = 0 ;
entkey = display_PutGet( csrLineRrn: sflCb ) ;
// exit screen.
if entkey = 'F3' ;
*inlr = '1' ;
leave ;
endif ;
// process subfile line changes.
cycleChgCx = 0 ;
subfile_ProcessLineChanges( sflCb:
cycleChgCx: topKey ) ;
chgCx += cycleChgCx ;
// process the enter key, the command key, rollup, rolldown.
processRv = display_ProcessEntKey( sflCb: entkey:
topKey: nxTopKey: csrLineRrn:
stats ) ;
topKey = processRv.topkey ;
if processRv.Chgd = '1' ;
chgCx += 1 ;
endif ;
enddo ;
*inlr = '1' ;
return ;
/end-free
** --------------------- display_ProcessEntKey ---------------------
pdisplay_ProcessEntKey...
p b
ddisplay_ProcessEntKey...
d pi likeds(refProcessRv)
d inSubfileCb likeds(refSubfileCb)
d inEntkey 10a const varying
d inTopKey likeds(refSflRowKey)
d inNxTopKey likeds(refSflRowKey)
d inCsrLineRrn 10i 0 const
d inStats likeds(refPerformanceStats)
d fChgd s n
d rv ds likeds(refProcessRv)
d seqnbr s 9p 0
d entkey s 10a varying
/free
clear rv ;
rv.topkey = inTopKey ;
// f21-command line
if inEntkey = 'F21' ;
pr_quscmdln( ) ;
// f5 - restart performance stats
elseif inEntKey = 'F5' ;
clear inStats ;
// f7-position subfile at seqnbr.
elseif inEntkey = 'F7' ;
exfmt pos3526 ;
if *in03 = '0' ;
rv.topkey = sflRowKey_Construct( scseqnbr ) ;
endif ;
// f8-add more rows to table
elseif inEntKey = 'F8' ;
if scNumRows = 0 ;
scNumRows = 5000 ;
endif ;
exfmt more3526 ;
if *in03 = '0' ;
dsh3526p_CreateMoreRows( scNumRows ) ;
clear inStats ;
endif ;
// f9-refill table with 100 rows.
elseif inEntKey = 'F9' ;
dsh3526p_Clear( ) ;
dsh3526p_CreateMoreRows( 100 ) ;
// in36 = page down.
elseif inEntKey = 'PAGEDOWN' ;
rv.topkey = dsh3526p_pageDown( inTopKey:
inSubfileCb.pagSx ) ;
// page up
elseif inEntKey = 'PAGEUP' ;
rv.topkey = dsh3526p_pageUp( inTopKey:
inSubfileCb.pagSx ) ;
endif ;
return rv ;
/end-free
p e
** ------------------------- display_PutGet -------------------------
** write and read the display record formats.
pdisplay_PutGet b
ddisplay_PutGet pi 10a varying
d outCsrLineRrn 10i 0
d inSubfileCb likeds(refSubfileCb)
d entkey s 10a varying
/free
// write the screens.
write ctl3526 ;
write ftr3526 ;
write hdr3526 ;
// read the input screens.
read ctl3526 ;
read hdr3526 ;
entkey = sub_IndToFuncKeyname( *in: 36: 37 ) ;
// store the rrn of line where cursor is located.
outCsrLineRrn = rrn10 ;
return entkey ;
/end-free
p e
** ------------------------- dsh3526p_Clear -------------------------
pdsh3526p_Clear b
ddsh3526p_Clear pi
/free
exec sql
delete from dsh3526p ;
/end-free
p e
** ------------------------ dsh3526p_PageDown ------------------------
pdsh3526p_PageDown...
p b
ddsh3526p_PageDown...
d pi likeds(refSflRowKey)
d inTopKey const likeds(refSflRowKey)
d inSflPagSx 10i 0 const
d vSeqnbr s 9p 0
d vRownum s 10i 0
d rowkey ds likeds(refSflRowKey)
/free
exec sql
with t1 as (
select a.seqnbr
from dsh3526p a
where a.seqnbr > :inTopKey.seqnbr
order by a.seqnbr
fetch first 20 rows only ),
t2 as (
select a.seqnbr, row_number( )
over( order by a.seqnbr ) rownum
from t1 a )
select a.seqnbr
into :vSeqnbr
from t2 a
where a.rownum >= :inSflPagSx
order by a.rownum
fetch first row only ;
if sqlcode = 0 ;
rowkey = sflRowKey_Construct( vSeqnbr ) ;
else ;
clear rowkey ;
endif ;
return rowkey ;
/end-free
p e
** ------------------------- dsh3526p_PageUp -------------------------
pdsh3526p_PageUp b
ddsh3526p_PageUp pi likeds(refSflRowKey)
d inTopKey const likeds(refSflRowKey)
d inSflPagSx 10i 0 const
d vSeqnbr s 9p 0
d vRownum s 10i 0
d rowkey ds likeds(refSflRowKey)
/free
exec sql
select a.seqnbr, a.rownum
into :vSeqnbr, :vRownum
from (
select a.seqnbr, row_number( )
over( order by a.seqnbr desc ) rownum
from dsh3526p a
where a.seqnbr < :inTopKey.seqnbr
order by a.seqnbr desc ) a
where a.rownum >= :inSflPagSx
order by a.rownum
fetch first row only ;
if sqlcode = 0 ;
rowkey = sflRowKey_Construct( vSeqnbr ) ;
else ;
clear rowkey ;
endif ;
return rowkey ;
/end-free
p e
** ------------------------ dsh3526p_Ensure ------------------------
** make certain the dsh3526p table exists.
pdsh3526p_Ensure b
ddsh3526p_Ensure pi
d vSeqnbr s 9p 0
/free
exec sql
select a.seqnbr
into :vSeqnbr
from dsh3526p a
fetch first row only ;
// table does not exist. create it.
if sqlcode = -204 ;
exec sql
create table dsh3526p (
seqnbr decimal(9,0),
textLine char(100),
primary key(seqnbr)) ;
endif ;
/end-free
p e
** ---------------------- dsh3526p_GetLastRow ----------------------
pdsh3526p_GetLastRow...
p b
ddsh3526p_GetLastRow...
d pi likeds(refSflRowKey)
d vSeqnbr s 9p 0
d rowkey ds likeds(refSflRowKey)
/free
exec sql
select a.seqnbr
into :vSeqnbr
from dsh3526p a
order by a.seqnbr desc
fetch first row only ;
if sqlcode <> 0 ;
vSeqnbr = 0 ;
endif ;
rowkey = sflRowKey_Construct( vSeqnbr ) ;
return rowkey ;
/end-free
p e
** --------------------- dsh3526p_CreateMoreRows ---------------------
pdsh3526p_CreateMoreRows...
p b
ddsh3526p_CreateMoreRows...
d pi
d inNbrRows 10i 0 const
d cx s 10i 0
d vSeqnbr s 9p 0
d textLine s 256a varying
/free
// get last seqnbr
exec sql
select a.seqnbr
into :vSeqnbr
from dsh3526p a
order by a.seqnbr desc
fetch first row only ;
if sqlcode <> 0 ;
vSeqnbr = 0 ;
endif ;
for cx = 1 to inNbrRows ;
vSeqnbr += 1 ;
textLine = 'sample text line. seqnbr ' + %editc(vSeqnbr:'X') ;
exec sql
insert into dsh3526p
( seqnbr, textline )
values( :vSeqnbr, :textLine ) ;
endfor ;
/end-free
p e
** ---------------------- FillDsplyHdr --------------------------
pFillDsplyHdr b
dFillDsplyHdr pi
d inSubfileCb const likeds(refSubfileCb)
d inStats likeds(refPerformanceStats)
d elapAvg s 15p 5
/free
title = 'Performance Tester - Row_Number' ;
performanceStats_Update( stats ) ;
scElapSec = %trim(%editc(stats.elapSecs:'J')) ;
scPutGetCx = %trim(%editc(stats.putGetCx:'Z')) ;
elapAvg = stats.elapSecs / stats.putGetCx ;
scElapAvg = %trim(%editc(elapAvg: 'J')) ;
scRowCx = %trim(%editc(stats.rowCx:'Z')) ;
/end-free
p e
** ------------------------ initSfl -----------------------------
pinitSfl b
dinitSfl pi
d inViewNum 10i 0 const
/free
*in31 = '0' ;
RRN10 = *ZEROS ;
*IN35 = *ON ; // sflclr
*in32 = *off ; // condition pagedown
*in38 = *off ; // condition pageup
WRITE ctl3526 ;
*IN35 = *OFF ;
sflCx = 0 ; // nbr rcds in subfile.
pag10 = 0 ; // pos cursor sfl rcdnbr.
/end-free
p e
** -------------------- performanceStats_Update --------------------
pperformanceStats_Update...
p b
dperformanceStats_Update...
d pi
d inStats likeds(refPerformanceStats)
d elap s 20i 0
/free
if inStats.putGetCx = 0 ;
inStats.bgnTs = %timestamp( ) ;
endif ;
inStats.putGetCx += 1 ;
inStats.endTs = %timestamp( ) ;
// elapsed time
inStats.elapSecs = %diff( instats.endTs: inStats.bgnTs:
*mseconds ) / 1000000 ;
// number of rows in the table.
exec sql
select count(*)
into :inStats.rowCx
from dsh3526p ;
/end-free
p e
** ---------------------- sflRowKey_Construct ----------------------
psflRowKey_Construct...
p b
dsflRowKey_Construct...
d pi likeds(refSflRowKey)
d inSeqnbr 9p 0 const
d rowkey ds likeds(refSflRowKey)
/free
clear rowkey ;
rowkey.seqnbr = inSeqnbr ;
return rowkey ;
/end-free
p e
** ----------------------- subfile_GetSeqnbr -----------------------
psubfile_GetSeqnbr...
p b
dsubfile_GetSeqnbr...
d pi 9p 0
d inRowNum 10i 0 const
d inViewNum 10i 0 const
d seqnbr s 9p 0
/free
seqnbr = 0 ;
chain inRowNum sfl3526 ;
if %found = '1' ;
seqnbr = hdSeqn ;
endif ;
return seqnbr ;
/end-free
p e
** ------------------ subfile_ProcessLineChanges ------------------
psubfile_ProcessLineChanges...
p b
dsubfile_ProcessLineChanges...
d pi
d inSubfileCb likeds(refSubfileCb)
d inChgCx 10i 0
d inOutTopKey likeds(refSflRowKey)
d insertSeqnbr s 9p 0
d textLine s 256a
d lx s 10i 0
d fChgd s n
d fWasDeleted s n
/free
dow 1 = 1 ;
readc sfl3526 ;
if %eof( ) = '1' ;
leave ;
endif ;
// apply the text changes to the full text line.
lx = %size(sfText) ;
textLine = hdText ;
%subst(textLine:1:lx) = sfText ;
enddo ;
/end-free
p e
** ------------------------ subfile_WritePage ------------------------
** Write a page of rows from dsh3526p to the subfile.
psubfile_WritePage...
p b
dsubfile_WritePage...
d pi likeds(refFillSflReturn)
d inTopKey const likeds(refSflRowKey)
d inSubfileCb likeds(refSubfileCb)
d rowkey ds likeds(refSflRowKey)
d fillRv ds likeds(refFillSflReturn)
d ch5 s 5a
d vSeqnbr s 9p 0
d vTextLine s 100a
d ch20 s 20a
d ix s 10i 0
/free
clear fillRv ;
*in32 = '0' ;
exec sql
declare c1 cursor for
select a.seqnbr, a.textline
from dsh3526p a
where a.seqnbr >= :inTopKey.seqnbr
order by a.seqnbr ;
exec sql
open c1 ;
pagCx = 0 ;
pag10 = 0 ;
dow 1 = 1 ;
exec sql
fetch c1
into :vSeqnbr, :vTextLine ;
if sqlCode <> 0 ;
leave ;
endif ;
// page is full. seton indicator to condition rollup key.
pagCx += 1 ;
if pagCx > inSubfileCb.pagSx ;
fillRv.nxTopKey = sflRowKey_Construct( vSeqnbr ) ;
*in32 = '1' ;
leave ;
endif ;
// nbr of rcds in the subfile.
sflCx += 1 ;
rrn10 = sflCx ;
// show this sfl rcd in the subfile page.
if pag10 = 0 ;
pag10 = sflCx ;
endif ;
%subarr(*in:40:10) = '0' ;
ch20 = %editc(vSeqnbr: 'X') ;
// view 1. seqnbr, line text.
sfSeqn = ch20 ;
sfText = %subst(vTextLine:1) ;
hdSeqn = vSeqnbr ;
hdText = vTextLine ;
*in31 = '1' ;
hdinds = sub_IndGetFrom( *in: 42: 4 ) ;
WRITE sfl3526 ;
// save keys to the top sfl line.
if pagCx = 1 ;
fillRv.topKey = sflRowKey_Construct( vSeqnbr ) ;
endif ;
enddo ;
// close the cursor.
exec sql
close c1 ;
fillRv.sflCx = sflCx ;
// nothing written to subfile. reposition at last row in table.
if sflCx = 0 ;
rowkey = dsh3526p_GetLastRow( ) ;
if rowkey.seqnbr <> 0 ;
fillRv = subfile_WritePage( rowkey: inSubfileCb ) ;
endif ;
endif ;
return fillRv ;
/end-free
p e
** ---------------------- sub_IndGetFrom -------------------------
** get string of consecutive indicator values from *IN array.
psub_IndGetFrom b export
dsub_IndGetFrom pi 99a varying
d inInd n dim(99)
d inIx 10i 0 const
d inLx 10i 0 const
d pFlatArray s *
d flatArray s 99a based(pFlatArray)
/free
pFlatArray = %addr(inInd(inIx)) ;
return %subst(flatArray:1:inLx) ;
/end-free
p e
** ----------------- sub_IndToFuncKeyName ---------------------
** convert function key indicator to func key name
psub_IndToFuncKeyName...
p b export
dsub_IndToFuncKeyName...
d pi 10a varying
d inInd n const dim(99)
d inPageDownInd 10i 0 const options(*omit:*nopass)
d inPageUpInd 10i 0 const options(*omit:*nopass)
d entkey s 10a varying
/free
%len(entkey) = 0 ;
if %parms >= 2 and %addr(inPageDownInd) <> *null
and inInd(inPageDownInd) = '1' ;
entkey = 'PAGEDOWN' ;
elseif %parms >= 3 and %addr(inPageUpInd) <> *null
and inInd(inPageUpInd) = '1' ;
entkey = 'PAGEUP' ;
elseif inInd(1) = '1' ;
entkey = 'F1' ;
elseif inInd(2) = '1' ;
entkey = 'F2' ;
elseif inInd(3) = '1' ;
entkey = 'F3' ;
elseif inInd(4) = '1' ;
entkey = 'F4' ;
elseif inInd(5) = '1' ;
entkey = 'F5' ;
elseif inInd(6) = '1' ;
entkey = 'F6' ;
elseif inInd(7) = '1' ;
entkey = 'F7' ;
elseif inInd(8) = '1' ;
entkey = 'F8' ;
elseif inInd(9) = '1' ;
entkey = 'F9' ;
elseif inInd(10) = '1' ;
entkey = 'F10' ;
elseif inInd(11) = '1' ;
entkey = 'F11' ;
elseif inInd(12) = '1' ;
entkey = 'F12' ;
elseif inInd(13) = '1' ;
entkey = 'F13' ;
elseif inInd(14) = '1' ;
entkey = 'F14' ;
elseif inInd(15) = '1' ;
entkey = 'F15' ;
elseif inInd(16) = '1' ;
entkey = 'F16' ;
elseif inInd(17) = '1' ;
entkey = 'F17' ;
elseif inInd(18) = '1' ;
entkey = 'F18' ;
elseif inInd(19) = '1' ;
entkey = 'F19' ;
elseif inInd(20) = '1' ;
entkey = 'F20' ;
elseif inInd(21) = '1' ;
entkey = 'F21' ;
elseif inInd(22) = '1' ;
entkey = 'F22' ;
elseif inInd(23) = '1' ;
entkey = 'F23' ;
elseif inInd(24) = '1' ;
entkey = 'F24' ;
else ;
entkey = 'ENTER' ;
endif ;
return entkey ;
/end-free
p e


On Tue, Mar 24, 2015 at 2:08 PM, Steve Richter <stephenrichter@xxxxxxxxx>
wrote:

oddly enough, the "with" statement does not solve the problem. the more
rows the poorer the performance of row_number. I will post sample code
later ...

thanks,


Exec SQL
With x as (Select SeqNbr
From DSH3526P
Where .SeqNbr > :inTopKey.SeqNbr
Order By SeqNbr
Fetch First 20 Rows Only)
y as (Select Row_Number() Over(Order By SeqNbr), SeqNbr
From x)
Select SeqNbr, RowNum into :vSeqnbr, :vRownum
From y
Where SeqNbr > :inSflPagx
Fetch first row only;


On Tue, Mar 24, 2015 at 1:37 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

If you use ROW_Number() or an Order By in a Subquery and then select only
20
rows, the complete query must be executed first (i.e. all rows must be
sorted and numbered) before the desired (first 20) rows can be selected.
For
huge tables it may take a lot of time.

What if you execute first the sub-query to return the 20 rows you need, an
number the result in a second sub-query?
Something like this:
exec sql
select a.seqnbr, a.rownum
into :vSeqnbr, :vRownum
from (Select SeqNbr, Row_Number() Over(Order By SeqNbr)
rowNum
From (select a.seqnbr, row_number( ) over(order by
a.seqnbr ) rownum
from dsh3526p a
where a.seqnbr > :inTopKey.seqnbr
order by a.seqnbr
fetch first 20 rows only ) a) b

where rownum >= :inSflPagSx
order by rownum
fetch first row only ;

or (IMHO this is more readable)
Exec SQL
With x as (Select SeqNbr
From DSH3526P
Where .SeqNbr > :inTopKey.SeqNbr
Order By SeqNbr
Fetch First 20 Rows Only)
y as (Select Row_Number() Over(Order By SeqNbr), SeqNbr
From x)
Select SeqNbr, RowNum into :vSeqnbr, :vRownum
From y
Where SeqNbr > :inSflPagx
Fetch first row only;

Make also sure that there is an index (or logical file) with the SeqNbr
key
field.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Steve Richter
Gesendet: Monday, 23.3 2015 19:21
An: Midrange Systems Technical Discussion
Betreff: row_number performing badly in table with a lot of rows

experimenting with using row_number to implement a SKIP type function.
Where an inner query use row_number to assign a sequential number to the
selected rows. And then the outer query says WHERE rownum >= 20 to skip
over
the first 20 of the selected rows. The objective being to page thru the
rows of a table, either forward or backward.

Here is the code for paging forward:
exec sql
select a.seqnbr, a.rownum
into :vSeqnbr, :vRownum
from (

select a.seqnbr, row_number( )
over( order by a.seqnbr ) rownum
from dsh3526p a
where a.seqnbr > :inTopKey.seqnbr
order by a.seqnbr
fetch first 20 rows only ) a

where a.rownum >= :inSflPagSx
order by a.rownum
fetch first row only ;

The code works. And it performs well enough when there are up to 50,000
rows
in the table. But the more rows in the table the slower the execution.

How can I use row_number in this manner efficiently? I tried the code
where
I have a 3rd nested query which simply runs "fetch first 20 rows only",
returning those rows to the query which assigns the row_number which
returns
its result rows to the outer query. Same poor performance.

thanks,
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.




As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
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.