Re: Batch Billing Performance.

[fkolmann@xxxxxxxxxxxxx]

zieske@nexgensoftware.com wrote:

> > Reply to fkolmann@revlon.com.au:
>
> The point that creating a lgl can be detrimental I cannot understand.
> What this means to me is that the optimiser makes a mistake.
>
> An unnecessary logical does not really hurt SQL that much, (course it gives it
> one other option to always keep checking) The problem is the possible effect 
>on
> the entire system, not just SQL.  If logicals had no affect on performance the
> obvious thing to do to get a high performance system is to build separate
> logicals over every field and every possible access path in a file.

Harmon, thanks for the reply.
As someone who has worked on the AS400 since it was first sold and prior to that
S38's, I am well
aware of the costs of logical files, both in terms of disk space usage and 
database
performance on update operations.

The point I am trying to make is the Costs of SQL in programs.

SQL is an insidious little beast (colossal monster).

It is SO EASY to slip in dozens of SQLs into a program and every one of them 
could
be a potential database performance problem if the programmer was not careful 
in how
the sequence of fields is set in the SQL program statement.

Heck, I have see in the same program SQL statements that had as Keys ORDER/LINE,
LINE/ORDER, ORDER/LINE/ID,   ID/LINE/ORDER when the only lgl was keyed as
ID/ORDER/LINE.  As you can see the performance of the program was diabolical 
with
access paths created continuously when it would have been (was) a very simple 
matter
to sequence the fields in the SQL correctly.

The SQL coder had NO IDEA of the impact of his code on the system and when I
confronted him with this his attitude was that there was no restriction on the
system to this and he will code the way that is most suitable to meet the 
program
specifications.

All the statements about balancing performance now vs performance later are 
TOTALLY
IGNORED by the coders using SQL.  The creating of LGL files was always such an
obvious step and one always paid careful consideration to the costs vs the 
benefits
of creating them.  Considering that each SQL is effectively a new lgl file it is
downright irresponsible, the indiscriminate use of SQL.

Perhaps IBM can include in the SQL pre- compiler a summary list of lgl files 
needed
to support the SQL in a particular program.  One could then compare this list 
with
the available lgl files and try to get on top of this nightmare.

+---
| This is the BPCS Users Mailing List!
| To submit a new message, send your mail to BPCS-L@midrange.com.
| To subscribe to this list send email to BPCS-L-SUB@midrange.com.
| To unsubscribe from this list send email to BPCS-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: dasmussen@aol.com
+---