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.