John, I think this is where the RPG developer needs to step back and reconsider the problem...
With SQL, I get the opportunity to piece the data assembly requirements together into the SQL statement, allowing the database to deliver "just what I want" already assembled together and ready for business logic.
So, you have three chains to incorporate into your SQL design. I usually think of chain as a synonym for JOIN (inner, or left outer, depending on requirements). The technique of using setll (and %equal) in RPG to determine if the row exists is comparable to SQL's EXISTS clause... Putting all these elements together into your SQL statement removes a great deal of complexity from your application, letting you focus on processing of the transactions.
JMO
-Eric DeLong
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of John Allen
Sent: Friday, November 02, 2012 3:18 PM
To: 'RPG programming on the IBM i / System i'
Subject: RE: Should I replace all CHAIN, SETLL, and READs to SQL
Alan,
Thanks for the other point of view.
If I have an interactive program and it needs to chain to 3
files once, I was wondering if the user would notice a
difference in performance
Using SQL versus a chain.
(And I am not concerned about thousands of a second because
the user would not notice that)
I definitely want to get away from "hardwiring the physical
view", I hate it when I have to recompile (and test) several
programs just because I add a new field
And I definitely don't want to have a whole bunch of
logicals.
Thanks for your point of view
John
-----Original Message-----
From: Alan Campin [mailto:alan0307d@xxxxxxxxx]
Sent: Friday, November 02, 2012 3:00 PM
To: RPG programming on the IBM i / System i
Subject: Re: Should I replace all CHAIN, SETLL, and READs to
SQL
But in return for that 100 millionth of seconds of
performance you are hardwiring the physical view of the
table into the program. You have lost your database
independence for a few 100 millionth of second performance
and as I have said over and over the whole performance thing
starts going out the window when you begin to consider
multiple tables. Using RLA, you chain this one and then that
one and then this one. In SQL you are almost always bringing
fields from multiple tables and all your performance
measurement go out the window.
On Fri, Nov 2, 2012 at 12:40 PM, Nathan Andelin
<nandelin@xxxxxxxxx> wrote:
Did you benchmark SELECT COUNT(*) FROM XXXX WHERE..... ?
Yes, select count(*) into :found ... consumes a little bit
more CPU
than select 1 into :found ...
The nice thing about select count(*) is that if a record
is NOT found,
then "found" contains 0, whereas select 1 into :found
always places 1
in found.
Again, if you want best performance, use SETLL to check
for existence.
-Nathan
--
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.
midrange.com
