Re: More Efficient Processing ideas...

["Jim Franz" <jfranz@xxxxxxxxxxxx>]

I've used SQLRPGLE to build online inquiries over 5 million plus records,
user has
11 different fields to select and can use any combination. Some selects are
even
ranges or contains (like). The rpgle dynamically builds
the select string. I did analyze which fields (like customer# or date range)
narrow the
selection, and built 8 logicals that cover it, and always build the sql
select to match
one of the views. It's much more efficient than having a query/sql
dynamically build
a temp index. We have users banging on this all day on a very small server.
jim
----- Original Message ----- 
From: <fkany@xxxxxxxxxxxxxxxxxx>
To: <RPG400-L@xxxxxxxxxxxx>
Sent: Wednesday, September 17, 2003 6:14 PM
Subject: More Efficient Processing ideas...


>
> Currently my program below reads every single record in the file to see if
it matches certain criteria before processing a record.  There are millions
> of records in this file and it takes the program forever to complete.  The
criteria for checking each record depends on whatever the user enters.  So
> far there's
> about 5 or 6 criteria it must meet.  1 happens to be a date range, another
is customer number, billing numbers, etc...  I don't want to create any
> logicals since
> the criteria can change, but I'd like to have the subprocedure read a file
that's already sorted.
>
> Would executing an OPNQRYF statement via QCMDEXC before executing the
subprocedure be the best route?  SQL? other ideas?
>
>      P FILE_PRCS        B
>       *
>       /FREE
>
>            // Read FB File
>            SETLL *LOVAL FILE_A;
>            DOU %EOF(FILE_A);
>            READ FILE_A;
>
>                 // End of File
>                 IF %EOF(FILE_A);
>                 LEAVE;
>                 ENDIF;
>
>                 // Validate Record
>                 IF BLAH BLAH = BLAH BLAH etc...;
>                 PROCESS_RECORD();
>       ELSE;
>       ITER;
>       ENDIF;
>
>             ENDDO;
>
>       /END-FREE
>       *
>      P FILE_PRCS        E
>
> _______________________________________________
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>