Re: performance loading a subfile

["paul cunnane" <paul@xxxxxxxxxxx>]

> With no filter set the subfile loads quickly and happily.  However if the
> user chooses to filter out  99.5% of the records then loading the 10
> records may take thousands of reads to find 10 that pass.  In this case
> the performance is dreadful.  The selection is worth it so we wish to do
> it but is there some way to display records as we go, or choose a line
> before the whole subfile page is full?

This looks like a great excuse to use embedded SQL.  If no filter is set,
declare a cursor for SELECT...FROM File.  If there is a filter, declare the
cursor for SELECT...FROM File WHERE Field=Filter.  Then make sure your file
has an index on the filtered field, and presto!

One neat aspect of this approach is that it makes it relatively trivial to
add filters, should the need arise.  Simply add AND conditions to the WHERE
clause, and let the query optimiser figure out the best way to access the
records.

--
Paul


+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---