RE: Green screen - it's time is over

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

> -----Original Message-----
> From: Walden H. Leverich
>
> Given that 80%[1,2] of systems are input and
> 20% are update I think SQL has it's place for sure.

No argument here, although there are still places where a server beats SQL,
primarily in retrieving hierarchical data.  Let's say I have a hierarchy
with five different levels: customer, order header, order detail, comments
and shipment history.  I can do a server retrieve and have it return all the
data for a given customer in hierarchical order (customer 1, customer 1
order 1, customer 1 order 1 detail 1, customer 1 order 1 detail 2, and so
on).  Much easier with a server.  You can do it in SQL, but you either need
a massive JOIN (with all the inherent overhead of duplicated data) or you
have to retrieve multiple result sets, in which case you lose the implicit
order.  This is even more important if the data is not keyed, but instead
needs to be processed FIFO.


> 2) Your logic holds ONLY for single row updates. As soon as you hit
> multi-row updates, or updates based on the result of a select if
> it's all in
> one statement, SQL will kick native-io's ass every time. Reason is simple,
> native-io is driven from within the RPG (Cobol) program, you have to
> traverse into MI for each update. However, SQL is already below the MI and
> it stays there until the update is complete.

Again, it depends.  If you are doing the same exact calculation on a block
of records, sure, SQL may be faster.  This holds especially true in batch
updates.  But in a transaction based environment, a typical transaction may
update A/R, A/P, inventory, sales history, production history, allocations -
and this may be selectable by the user on a transaction by transaction
basis, using soft flags in the database.  I suppose you could code an SQL
statement for every combination, but I have a feeling that the standard SQL
approach to this sort of thing is not unlike the standard approach of RLA:
get a record, see what flags are set, get another record baed on that,
update it, get another record, and so on.  These are typically individual
FETCH INTO statements or SELECT FOR UPDATE cursors.  In my benchmarks,
cursors have consistently been slower than SETLL/READE.

Now, if you've designed your application and your database in such a way
that your transactions are all variations of UPDATE ... WHERE statements,
then SQL may indeed function close to, or perhaps even better than, RLA, but
unless I'm way off base, that's not the typical business application.

Joe Pluta
www.plutabrothers.com