Alan,

If your example you're not simply replacing chain with the SQL RLA
equivalent...you've replaced RLA with a set based operation.

SQL is perfect for that.

Charles

On Wed, Apr 4, 2012 at 4:54 PM, Alan Campin <alan0307d@xxxxxxxxx> wrote:
But in a real world example that I included SQL would be faster. Using
RPG I am chaining the entire units record in, then I am chaining in
the TRANSFER_CONTROL table and then checking the dates to see if they
are in range and then chaining in the UNITS_TRANSFERRED record to see
if it already there.

In contrast, SQL sends down the UNIT code and all the operations are
done at the database level, the lowest level of the OS. Anything you
push down to the database level is going to be faster than bringing it
all up into your program.

If IBM would simply fix SQL to use service programs instead of program
calls you could probably eliminate most of the difference anyway.

On Wed, Apr 4, 2012 at 2:33 PM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:
Alan,

I'll admit I haven't run the benchmarks on any recent hardware or OS
level...maybe I'll dig some up and run them.

But the fact of the matter is a SQL SELECT INTO could only ever be as
fast as an RPG CHAIN....never faster...and it certainly wasn't even as
fast a few years ago.  Perhaps that's changed.

Still, IMHO it's pointless to simply replace RPG RLA with SQL RLA....

Replacing RPG RLA with SQL set operations is another matter entirely.

Charles

On Wed, Apr 4, 2012 at 4:22 PM, Alan Campin <alan0307d@xxxxxxxxx> wrote:
"with SQL is usually a bad idea since SQL isn't conductive to
record at a time operations"

Based on my experience I believe this is a myth. We have hundreds of
programs doing single I/O operations using SQL and I have yet to see
any real problem. You might do a test and have a few milliseconds
slower but in a real world environment does that really matter?

On Wed, Apr 4, 2012 at 2:14 PM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:
I'm with Scott, the only reason to encapsulate a file is to
encapsulate business logic with the file i/o...  Your example is a
perfect example of this.   Even the simplest logic can/should be
encapsulated...I once had a master file with an active/inactive flag.
There was code all over the place checking that flag.  Then the
business decided that to be (in?)active, not only did the flag have to
be set but some other criteria needed to be met...

Would have loved to simply make a change to the ITEM_isActive()
procedure instead of finding every line of code that had IF ITMACT =
'Y'

Having your own procedures that simply mimic RPG RLA operations is
pointless...the only benefit it provides, being able to replace RPG
RLA with SQL is usually a bad idea since SQL isn't conductive to
record at a time operations (as you've found out).  The other
"benefit" often branded about, "when it comes time to implement a file
change, having that file in one location...." is oftentimes broken as
the data structures passed back and forth are tied the file.  Besides,
you can easily accomplish the goal of minimizing re-compiles by simply
enforcing the rule that all file access is done through a logical with
an explicit field list.  Now you can add fields to your hearts content
and not have to recompile anything.

Finally a word about your SQL example...using a cursor to fetch a
single row, ie. CHAIN, is not the right way to do it.  Instead, you
should be using a SELECT INTO.   However, even SELECT INTO isn't going
to be quite as fast as CHAIN.

Remember my motto, if you're using a cursor, you probably shouldn't
be.  :)  Yes, there are places where you have to use a cursor, loading
a display subfile or printing a report...but otherwise look for a non
cursor approach.

Charles


On Wed, Apr 4, 2012 at 3:29 PM, Kurt Anderson
<kurt.anderson@xxxxxxxxxxxxxx> wrote:
First off, we don't encapsulate all of our files.  Only the ones that have business logic directly associated with the file.
For example:
We have a file with records with effective dates associated with them.  We also have some clients who apply grace periods to their effective dates (that we need to interpret and apply on the fly if a record wasn't found w/o the grace period).  Without this service program, this file would be used in over a hundred programs.  Instead of having every program that needs to use this file have to go through all the gyrations of looking for the effective record, it's all handled in the service program.  I also call this service program a business unit.    The service program is set up so all the caller has to do is code the statement "line = getEffectiveLine( date: otherinfo );  "
--
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.

--
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.

--
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.

--
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.


This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].