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

> -----Original Message-----
> From: Joe Pluta
> Sent: Friday, August 17, 2001 11:01 PM
>
> Which further begs the question: what exactly is "set based processing"?
> When do you do set based processing?  For queries?  For update?
> Do you use
> set-based processing when you're allocating inventory?  Or calculating
> prices?  Or doing MRP generations?

Yes, set processing is used in queries (which you know), but also for
update, which you may not be as familiar with. The type of update that you
do with native access is called a positioned update, wherein you are
updating exactly one row based on the current position of the cursor. SQL
can certainly handle a positioned update, but it goes one step further. Set
based processing involves another type of update called a searched update.
It is used to update one or more rows, and may include a search condition.
For example:


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


Of course, you can achieve the same result using native access, but you're
still processing a set. The only difference is that using relational
calculus, you're telling the system *what* to do. With native access, you
need to also tell it *how* to go about doing that.

> "The primary goal of the optimizer is to choose an implementation that
> quickly and efficiently eliminates the rows that are not interesting or
> required to satisfy the request. Normally, the query optimization
> is thought
> of as trying to find the rows of interest. A proper indexing strategy will
> assist the optimizer and database engine with this task."
>

Yes, this is a key concept. Set processing usually includes the
eliminatation of unqualified rows, even when using native access.

> 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.


> Now, here's the example they point out:
>
> SELECT CUSTOMER_NAME, ORDERNUM, ORDERDATE, SHIPDATE, AMOUNT
> FROM ORDER_TABLE
> WHERE SHIPDATE IN ('2000-06-01', '2000-07-01', '2000-08-01')
> AND AMOUNT > 1000
>
> Um, okay.  That's an important use of SQL, but it's not one I use in
> transaction processing.  In most cases, transaction processing involves
> processing all the records in a given keyed subset, and doesn't involve
> field-level comparisons to eliminate rows (at least not if I know how to
> design my database and write my code).

I think you're taking a very narrow view to try to make a point. I don't
know of any systems that consist of nothing more than processing a series of
transactions. Even the clerks that handle strictly transaction based work
(inventory adjustments, order entry, etc.) commonly have a requirement to
work with a given subset of those transactions.


> 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 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"?



John Taylor





>
> I guess because I don't believe in magic, I'd like to know what the basic
> concept is that allows SQL to outperform an indexed read for pure
> transaction processing tasks.
>
> Joe
>
>
>
> > -----Original Message-----
> > From: John Taylor
> >
> > Joe,
> >
> > I agree with you if you're talking about single record key
> > positioning, but
> > READE/SETLL/OPNQRYF imply (to me) set based processing. SQL can often
> > outperform native access for sets. Of course, OPNQRYF itself
> uses the same
> > underlying SQL support, so I wouldn't have included it in the
> > same family as
> > the native opcodes.
> >
> > Here is a link to an interesting read about indexing in
> DB2/400, if you're
> > interested in nitty-gritty details:
> >
> > http://www.iseries.ibm.com/developer/bi/documents/strategy/strategy.pdf
> >
> >
> > John Taylor
>
> _______________________________________________
> 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
>