RE: SQL Update in RPGLE.

> From: DeLong, Eric
> 
> The HLL advantage comes from being able to rapidly get access to a record
> in
> a table.  The real issue here is "what is a transaction".  If the
> transaction involves a great deal of file IO, then SQL will probably
> outperform native access.

This is only true if the I/O can be encapsulated into sets of like actions.
The problem is that this is rarely the case.  The example that's often used
is "adding 5% to list prices".  Well, how often do you actually add 5% to
all the prices in a catalog?  The answer is "never".

Now if you have a small file of item number and price increments, it will
almost certainly be faster to do an UPDATE statement in SQL that applies
that increment to all the items.  However even this is not a perfect
example, you have to take into account the time it took to write the records
to that little file in the first place.

Generally speaking, it's hard to find cases in OLTP where the number of
records in the set justifies using SQL.  Typically the only place in an
interactive environment where SQL makes sense is in queries.

In the end, though, it's all in the application design.  I have long had a
private belief that a properly designed database might make an SQL MRP
generation fly; not only that, it would be able to take care of DRP
requirements at the same time.  But I've yet to design such a database nor
seen anyone else do so.  Until such time as we manage this sort of
application design, SQL is still best used for queries.


> There's no doubt that SQL can outperform native in many circumstances, but
> there's no guarantee that this is always the case.  The DB2 developers
> have
> done a great job of optimizing SQL over the last few releases, and it's
> harder to make sweeping generalizations about native IO being faster than
> SQL...

No, it's still pretty easy.  Single record chains and updates are faster in
native I/O than SQL.  Period.  And the break even point for sets seems to be
about ten records for a read and 100 records for update, although those
numbers are definitely subject to the ongoing optimizations (they used to be
much higher).

Joe