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

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

I think you've touched upon a very valid point, Jim, and it might be what
underlies my native distrust of SQL for database update.  In all of my
industrial-strength applications design - and I base this primarily on my
years with Navistar and with SSA - all but the most trivial of transactions
involved a combination of reading, writing and updating records at various
levels within the data hierarchy.  Be it a bill of materials, a general
ledger account, or a customer order, there were numerous pieces of data with
lots of one-to-many relationships that had to be accounted for.

SQL, with its set-based approach, isn't particularly well suited for the
hierarchical nature of the business data I'm accustomed to dealing with.
While it's relatively easy to perform operations on a single level within
the hierarchy, it's all but impossible to codify the updates among the
various levels without resorting to multiple SQL statements, each acting on
a specific level.  The only other option is do directly translate the
hierarchical database operations into SQL statements using cursors and
UPDATE WHERE CURRENT types of instructions, which are slower and more
complex than the native I/O they replace.

So again I find myself thinking that the correct approach may involve
separating database updates into set-based and record-based operations.  SQL
can be used to advantage for the true set-based updates, and in conjunction
with triggers may even allow the types of auditing that you would need
during a mass price update.  At the same time, the more traditional
transaction processing would be performed using external I/O modules written
with native I/O techniques.  Again, triggers could be used where
appropriate, especially for logging.

Interesting, interesting, interesting.

Joe