RE: SQL Update in RPGLE.

> From: Wilt, Charles
> 
> Given a level playing field, SQL will always beat RPG when dealing with
> result sets of more than a handful of records.

This has always been the case, and will always be the case.  It's common
sense as long as you understand the mechanics.  On one hand is the play
between the various layers of the OS, while on the other hand is the
quickness of initiating the operation.

It's kind of like satellite communications.  You can have blazingly fast
transmission speeds, but you still get killed because of lag time.  Unless
you're sending large buffers of data, the lag time for getting a connection
from the satellite kills you (remember, sending a single byte to and from a
satellite requires a round trip of >44000 miles, or about 1/4 second).  The
same with SQL.  The overhead on single-record functions can be overcome by
the speed savings of multi-record updates.

At the end of the day, you read and/or update a record.  If you're only
doing one, then you have to traverse all the layers for that record and the
delay is the same for SQL or native I/O.  However, native I/O has much less
in the way of setup overhead (this is especially true for dynamic SQL), so
is faster.  However, when multiple records are involved, the operation gets
to stay lower in the OS and so the transaction overhead is less.  At some
point, the lowered transaction overhead outweighs the setup overhead, and
SQL wins.

On DB2/400 on an i520, my last measurement at V5R3 showed SQL catching up
around 10 reads and 100 updates (this was without prepared statements,
IIRC).

Joe