Re: PF Compiled Files with Dictionaries vs. SQL-Created Tables

I recommend that you read it. It is not about running SQL vs. RLA, but about
the relative performance of SQL-defined tables against DDS-defined files in
a "conventional" (RLA) RPG data processing program.

As in everything, the answer to the debate of SQL vs. RLA is: "it depends".
If you are using record set processing, SQL (with the proper indexes) will
be almost always faster (also, IMHO, easier to write and document for
complex queries).
If you are processing small sets of data (1-100 records), RLA should be
faster.

There are, of course, many parameters involved and, in the end, it depends
of your shop, standards, etc.

Regards,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--



On Wed, Feb 2, 2011 at 10:48 AM, hockchai Lim <lim.hock-chai@xxxxxxxxxxxxxxx

wrote:

didn't read the article. But I do know that IBM and some folks have
claimed
that SQL is faster than RLA. But from my real live experience, it is
hardly
the case. I use embeded SQL in RPG applications quite frequently. Almost
in all cases, the response time in those applications are either about the
same or worse than the RLA version. May be I didn' t create the SQL
statement correctly, may be I need to create some indexes to make it run
faster, or may be I need to fetch in block... so many possibilities. Now
who is got the time to do those guess work :). Don't get me wrong, I love
using embeded SQL in RPG. But I just don't see it perform better than RLA.


"Gary Thompson" <GThompson@xxxxxxxxxxx> wrote in message
news:mailman.26728.1296655165.2702.midrange-l@xxxxxxxxxxxx...

You might want to know about relative performance of tables created using
SQL DDL vs DDS:

http://www-03.ibm.com/systems/resources/systems_i_software_db2_pdf_Performance_DDS_SQL.pdf

CRPence <CRPbottle@xxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
02/01/2011 06:05 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
midrange-l@xxxxxxxxxxxx
cc

Subject
Re: PF Compiled Files with Dictionaries vs. SQL-Created Tables






On 2/1/11 1:13 PM, hockchai Lim wrote:

I know I'll probably get blasted for saying this. But I must admit
that I've never been a big fan of using SQL to create tables. I
understand that using SQL will allow me to create table with feature
that is not supported by DDS (such as Identity column). But that is
IBM's fault not mine :). The main reason I didn't like SQL is because
it strains my eye to read that SQL create table statement. DDS is
just much neater and easier to read.

And DDS even supports some features which SQL does not. I find
little value to change from DDS to use SQL DDL for existing database
*FILE objects, without some obvious need to access some new feature
provided by the TABLE; e.g. either data types supported only in the SQL,
or better integrity for numeric data. Without any specific goal to be
achieved by changing, what point is there in change except change for
the sake of change? That is, change without justification is
intuitively, not justified. Best to understand what will be lost and
what will be gained by the change, to avoid remorse for having made the
jump, and even more regrets for having to move back in order to
"recover" from the first change. Changing "just because" to using SQL
DDL versus DDS is, for lack of a better word, daft.

While many DDS PF source may be very clean and concise, I mostly
disagree that DDS specifications would be generally easier to read than
the SQL, except to those who have some experience coding to fixed-format
source specifications. I think difficulty in reading the SQL DDL would
probably be caused mostly by poor formatting of the statements,
exacerbated by [typically self imposed] limitations to line length.
FWiW the LABEL ON statement for column headings and text being moved
outside the functional field definitional attributes could be considered
by many to be much nicer than having them intermingled and even
line-wrapped from and back into the functions area of a DDS source

member.

Regards, Chuck
--
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: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
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: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.