Re: RPG SQL and Cursors

As usual with the AS/400 everyone in these discussions is ignoring the 300
pound gorilla in the room, database independence.

When you use RLA, unless you externalize your I/O somehow (which almost no
one know how to do and won't use it even if you did), you are bringing the
entire record into the program which in effect, locks the table to change.

That means the next time you need to change the table you have to recompile
every program that uses it and very quickly no wants to recompile 500
programs so they start creating extension tables or reusing fields or god
knows what.

If you are using SQL and bringing only what you need that table is still
independent. You can add additional data elements and not have to recompile
the entire world.

So do you really want to save a few billionth of a second by using RLA and
then spend god knows how many programmer hours trying to get around the fact
that you have hardwired your database into every program in the system?

Database independence is the 300 pound gorilla in the room on the AS/400. If
I were to add up the cost just our company has paid it is unreal. The
solutions that people have come up with to avoid recompiling the entire
world have created so many problems it just blows your mind.

Just an example, we have a LOAD table. When the system was first designed no
one had the idea of pre-dispatching but later on they realized they needed
to pre-dispatch orders but they had no flag in the LOAD file that said
status (Ready, PreDispatch, Dispatched, etc) so the solution was to create a
LOADH table and now we have code everywhere looking at two tables and moving
data, etc, etc.

If we had used SQL, we would have added one field and been done with it.The
hours spent on this and resulting messes range into the thousands of hours
of programming time.

Funny thing about those gorillas. They always seem to be invisible. No one
every sees them.

On Thu, Nov 18, 2010 at 3:14 PM, Rory Hewitt <rory.hewitt@xxxxxxxxx> wrote:

Aaron,

I assume the point is not in terms of what you might do with them *within
your program* - as you point out, you're almost always going to process
them
row by row anyway, and that's a tiny percentage of processing. More
important (in terms of performance and related factors) is *how* you
retrieve them from the database - (very) roughly speaking, whether you
retrieve a set of records via SQL or a single record (either via SQL or
RLA), there is an equivalent cost. It's slightly more to retrieve a set of
records than it is to retrieve a single record, but not much so, and
*way*less than the cumulative cost of retrieving the records
individually...

In terms of relative performance cost, it's (again, very, very, very
roughly):

Retrieve a single record via RLA: 1
Retrieve a single record via SQL: 1.2
Retrieve a block of 10 records via SQL using a cursor: 3
Retrieve 10 records individually via RLA: 10
Retrieve 10 records individually via SQL: 12

Therefore, if you know that you're going to need a block of records, it
makes sense to pay a one-off price up front to get them all, rather than
retrieve them individually, using either SQL or RLA. the only time
individual retreival makes sense is if you only need a few random records
(i.e. CHAIN), at which point RLA might make sense.

The numbers above are definitely for illustration purposes only. They're
extremely rough and based on *way* out of date data....

Rory

On Thu, Nov 18, 2010 at 1:18 PM, Aaron Bartell <aaronbartell@xxxxxxxxx

wrote:

Now you have me curious. What do you do with a set of records as a whole
vs. process them row by row? It would seem to me that at some point a

row

needs to be processed by iteself.

Aaron Bartell
www.MowYourLawn.com/blog
www.OpenRPGUI.com
www.SoftwareSavesLives.com

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.