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.
Don't take this the wrong way, but you do have an index/logical over
SeqNbr, as Birgitta stipulated, right?
Now, I believe that in principle, there must be a way to get the
paging to have good performance regardless of table size. Further, it
seems odd to me that the *table size* should have any effect
whatsoever on the paging speed. (I'm sure this is the main mystery
for you and whoever else is reading this.) I would be much less
surprised if the performance somehow got slower as you paged further
and further away from the initial SeqNbr. But judging from your other
posts, I don't think this is what you are experiencing.
So, have you considered (horror of horrors) using RLA? Sure, it feels
old-school, primitive, and crude. But we always know exactly what's
happening with RLA. Paging maps very straightforwardly to RPG
looping.
The SQL engine is powerful and sophisticated, but much less
transparent. And it insists on doing things its way, so if you want
it to do something differently (or it has a bug you need to work
around) you have to contort the way you ask for things to manipulate
it into doing your bidding.
Again, I'll stress that I think an SQL-centric approach *should* work,
and work well. But clearly it's not. Now, I haven't looked too
closely at your code, and honestly my SQL skills are not really up to
the task anyway. So I can't tell if you have some mistake in your
code. But if it turns out that you did everything the way you're
supposed to, and it still exhibits this problem, it might be worth
thinking about (for now) shifting some of the work to pure RPG.
Oh, I guess I should also mention: Are you up to date with PTFs?
(Trying to channel Rob, I guess.)
John Y.
midrange.com
