Re: Database I/O Modernization

Nathan,

Pretty much completely wrong...

Originally, SQL & RLA did use the same interface to the DB, that's what's
known now as the Class Query Engine (CQE).

But SQE was completely new, lots of stuff was added below the MI to handle
SQL operations; taking advantage of the tight integration between DB & OS.
As mere mortals working above the OS, we can't take advantage of any of it
with RLA.

End result, SQL is faster and will continue to get faster than RPG RLA.

Can you have a poorly designed SQL process out performed by RPG RLA? Sure.
Can you have certain single record operations where RPG RLA is faster than
SQL? Possibly.
Can you simply replace RLA op-codes with SQL statements and see a
performance improvement? Probably not.

But take a properly written RPG process and replace it with a properly
written (and tuned) SQL one, and you will see big improvements.

Yes, SQL has some overhead, but it's quickly outweighed by the performance
gains available.

If you ever have a chance to take IBM's SQL Performance Workshop, I highly
recommend it.

Charles




On Fri, Sep 15, 2017 at 12:01 PM, Nathan Andelin <nandelin@xxxxxxxxx> wrote:

In regard to the performance of SQL vs. RLA, perhaps the most relevant fact
is that the SQL Query Engine uses RLA under the covers. Not the same QRNXIO
procedure calls that are made when invoking RPG I/O opcodes, but a
comparable RLA API. A number of IBM i components use the RLA API exported
from the QC2IO service program, but I haven't taken the time to see what is
used by SQL programs.

In the event that an SQL query outperforms RLA code, then it was probably
due to some misguided or bone-headed thought process on the part of the
programmer. Maybe he was using program joins rather than an external join
provided by a logical file.

The SQL query engine always adds overhead while attempting to figure out an
optimal access methodology, to perform mapping between DB record buffers
and cursors.

The justification for SQL is convenience. It can do a lot under the covers
that would otherwise require a lot of code. The justification for SQL is
programmer productivity - not performance.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD