RE: Reduce large amount of logicals in SUBFL pgm, take in another direction

Amazing how bad code looks after a while. I wrote most of this in 2002
to do basically the same thing you are talking about. We have a file
with 76 fields and we allow the clients to search based on about 70 of
them. The code at http://code.midrange.com/f766238a24.html shows
snippets of the way I did it. The file isn't very big - 2-3 thousand
records at max, so we never really bothered creating a lot of indexes.
We have about a dozen that handle the common searches, and just let the
system handle the rest.

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Sharon Strippoli
Sent: Friday, July 29, 2011 10:03 AM
To: RPG programming on the IBM i / System i
Subject: Re: Reduce large amount of logicals in SUBFL pgm,take in
another direction

Thanks for the input everyone,

while doing my research through this program someone who didn't
understand
logicals made a bunch of keys at the top of the pgm that list 3 fields
then
the next key has those same 3 plus another field. I haven't checked the
section of code in detail where these are used, but I assume if there
are
keys set up like that someone made logicals that match, instead of
making
one with 6 possible hits and also use it when you only have the first 2
or 3
fields. So I figure I can get rid of lots of logicals, but not sure
yet.

My Manager has researched the usage by user session and some folks input
lots of data to search against. We have discussed grouping some fields
so a
query would have say at least 4 or 5 pieces of data to go against, and
could
group query builds upon this predetermined set of fields. And then
educate
the users to scroll up or down to find their record, if it was not a
direct
hit.

But when thinking of how to set up the SQL statement, is there a
streamline
way to set it up? I need to check if input fields are populated and
organize how to build based on how many pieces of info I have to select
against. I am looking at how many groups of logicals I have based on
repeating key fields.



Sharon Strippoli
Pilot Freight Services
IT Dept.
Phone (610) 891-8113
sharonstrippoli@xxxxxxxxxxxxxxxxx
http://www.pilotdelivers.com




On Fri, Jul 29, 2011 at 9:55 AM, Morgan, Paul
<Paul.Morgan@xxxxxxxxxxx>wrote:

Sharon,

Use SQL as others have recommended but you might not be able to get

rid of

all those logicals. SQL would still need them depending on your

selection

and sorting.

I'd make sure all those logicals don't do any select/omits so SQL can

take

full advantage of them.

See if some of those logicals are redundant. If you have a logical

keyed

on fielda and a logical keyed on fielda and fieldb you could delete

the

redundant logical with the shorter key.

If you are doing some sort of summarization and your data doesn't

change

that much you could pre load a work file with the summaries then run

your

queries against the summarized work file instead of the full data set.

Do they really select on all those fields and other criteria? Do they
really select on some fields then refine their selection on other

fields and

criteria? I'd think about adding some logging to see what selection

and

other criteria is actually being used and how they 'drill down'.

Paul Morgan

Principal Programmer Analyst
IT Supply Chain/Replenishment

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx

[mailto:rpg400-l-bounces@xxxxxxxxxxxx]

On Behalf Of Sharon Strippoli
Sent: Friday, July 29, 2011 8:11 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: Reduce large amount of logicals in SUBFL pgm,take in another
direction

Good morning,

new job and given the assignment to revamp a screen pgm that the whole
company uses to make it more efficient, less CPU.

There are 12 different fields a user can fill in to search for the

info

they
want. Plus there are fields they can check off for other criteria.

Total

about 25 different fields on the screen.

Currently, there are about 80 logicals that are used for all the

different

combinations of fields to search on. The first SUBR of the pgm does
various
validations against all the possible fields, then depending on that

result

does a SETLL for the appropriate logical. Then a second SUBR checks

the

fields again and depending on that does a READ, READE or READP against

the

logical that was staged.

They want to select the data the first time through to a type of work

file,

then as more information is filled in by the user, query or read

against

that work file, drilling down further for each field that is populated

by

the user. They want to reduce CPU and the amount of logicals.
I was thinking of doing a query against the work file as it gets

drilled

down.

Wondering how the super users that use this forum would take this on.

Any

input would be appreciated.


Thanks.


Sharon





Sharon Strippoli
Pilot Freight Services
IT Dept.
Phone (610) 891-8113
sharonstrippoli@xxxxxxxxxxxxxxxxx
http://www.pilotdelivers.com
--
This is the RPG programming on the IBM i / System i (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.


--
This is the RPG programming on the IBM i / System i (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.