Re: Database I/O Modernization

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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