Re: SQL Paging Mechanism

[Paul Holm <pholm@xxxxxxxxxxxxxxxxx>]

Richard,

This can be a complex topic depending on your requirements if you need 
to factor things like cursor sensitivity, page forward, page back, cache 
for performance, retaining open locks, etc.

We solved this problem generically by creating a module/component called 
a "ROW COLLECTION" that remembers the SQL, the starting row index, 
ending row index, how many rows you want at a time, boolean for if more 
records exist, and a behavior to control cursor sensitivity.  Depending 
on your cursor config setting, when user asks to page down, the SQL is 
reexecuted and the result set cursor position is set the the "next" 
record. Row Collection is returned to the UI and automatically 
presented.  Next and Previous are generated based on if there are 
previous rows and more rows.  You can also retain the result set and 
continue paging down thru it but that retains a connection from your 
pool as well as a lock.

The resulting benefit is that we have not had to write any "paging" code 
for SQL applications for the past 8 years.  It was done once and is 
reused.  We use Java but the logic should be applicable to RPG, .NET, 
etc.

Page 21 of this presentation gives an illustration.
https://docs.google.com/open?id=0B2SXIgWQ9pn7bFpabkc2Tld4LW8

Here is an example if paging in action.  This app shows where AS400 
users are located geographically and optionally what software they are 
using.  I  put in paging examples for 10, 50, and 100 records at a time. 
Look under IBM i Paging menu group.

Page 10 records at a time.

http://www.planetjavainc.com/wowADDONDEV/runApp?opid=1311&_pj_lib=wowsamp

Page 50 records at a time.

http://www.planetjavainc.com/wowADDONDEV/runApp?opid=1310&_pj_lib=wowsamp

Page 100  records at a time
http://www.planetjavainc.com/wowADDONDEV/runApp?opid=1312&_pj_lib=wowsamp

Thanks, Paul Holm
IBM i Apps in Seconds:
www.planetjavainc.com



On 11/20/2012 5:44 AM, Richard Schoen wrote:
> I am about to put together an SQL based paging mechanism over a resultset that could be very large.
>
> Can anyone share their preferred mechanism for paging efficiently thru an SQL resultset without bringing back all records at the same time.
>
> I have played with putting a row number on each row and setting the starting row number and that seems to be OK, but any thoughts would be appreciated.
>
> Regards,
> Richard Schoen
> RJS Software Systems Inc.
> Where Information Meets Innovation
> Document Management, Workflow, Report Delivery, Forms and Business Intelligence
> Email: richard@xxxxxxxxxxxxxxx
> Web Site: http://www.rjssoftware.com
> Tel: (952) 736-5800
> Fax: (952) 736-5801
> Toll Free: (888) RJSSOFT