RE: sql exec speed. was AS/400 STILL wearing the undeserved "closed system" moniker

This is a fairly common algorithm in transportation (trucking), where
tariffs with thousands of rows and 10 columns (or more) are updated by a
fixed percentage.  I do it in RPG anyway because we require an audit trail
and have lots of conditions.  Yes, this is not a classic business
application but that doesn't invalidate the issue.

The LF "view" is a highly flexible design and implementation tool for
interactive applications.  I have dramatically improved batch application
performance by building LF's with the same select/omit and key field
specifications used in my OPNQRYF specification.  Yes, there is overhead
with MAINT(*IMMED), but it's an acceptable tradeoff because of the
processing window available and user constraints.

I like LF's; they're highly intuitive (or more intuitive than SQL coding)
and easy to share across programs and applications (which reduces the chance
of program errors caused by mismatched SQL coding).  Yes, SQL is the way of
the future, but like the SCUBA diving bumper sticker says, "Remember when
sex was safe and diving was dangerous?"

Regards,
Reeve Fritchman
reeve@ltl400.com


-----Original Message-----
From: midrange-l-admin@midrange.com [mailto:midrange-l-admin@midrange.com]On
Behalf Of Joe Pluta
Sent: Saturday, August 18, 2001 1:10 PM
To: midrange-l@midrange.com
Subject: RE: sql exec speed. was AS/400 STILL wearing the undeserved "closed
system" moniker

> update pricefile set unit_price = unit_price * 1.05
>    where product_type = "widgets"

I've seen this example in so many SQL references as to make me ill.  This is
a very rare occurence for every business I've seen.  The people who decide
what the prices are usually review sales and inventory figures on a case by
case basis, then decide to raise or lower their prices based on past
performance and seasonality factors.  In most cases, a list of new prices is
agreed upon in advance, and then these prices are applied, an item at a
time.  And even when wholesale changes like this are made (month-end
processing come to mind - great for rollovers!), they're not part of the
daily transactions that run the business.

Primarily, those sorts of statements are used in SQL 101 texts, and rarely
in daily production code.


> > Notice the goal of eliminating rows.  With a proper logical view,
> > there are
> > no rows to eliminate.
>
>
> Not true. It's just that the logical file is doing the
> elimination using the
> SELECT/OMIT fields for you, so that you don't have to code the
> logic in your
> HLL. Instead of specifying a WHERE clause in an SQL statement, you've
> specified SELECT/OMIT fields in a LF. Same amount of work, but the LF is
> less flexible.

The salient point is WHEN the filtering is done.  A logical file is an index
that contains only records that match the selection criteria.  The record is
added to or removed from the index when the record is written, not when it
is read.  This is called access path maintenance.  Unless you have
MAINT(*DELAY) specified for your access path, the logical file is always
entirely up to date, and contains only the records specified.  No comparison
is required during a read.  A dynamic query must build the access path on
the fly and eliminate unneeded records.


> > So, to my mind, proper database optimization can indeed speed access for
> > things like selecting, joining, grouping and ordering.  But I
> submit that
> > none of these are typical actions in a transaction processing
> environment.
>
> Typical? Maybe not, but required none the less.

Yes, and for those things, SQL is good.  For transaction processing, SQL is
bad.  For queries and wholesale data replacement, SQL works wonders, but
when programmers start using it to update data (especially a record at a
time), that's when I shake my head.


> > Yes indeed, for queries, customer support, executive information,
> > statistical analysis, and all things of that data mining ilk, query
> > optimization is a plus.  But how SQL can outperform a logical view for
> > non-constrained access is still a little beyond me.
>
>
> What do you mean be "non-constrained"?

Accessing data that does not require additional filtering, such as reading a
logical file.  Contrained access means you have to do further filtering on
your raw data set in order to eliminate rows.  A good percentage of
transaction processing does not require this sort of access, but instead
performs "non-constrained" I/O: every record read is used.

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com