Re: SQL data access through service program per file

Big complicated subject. Our company just went through some of the same
things and parts of it turned into a disaster.

1. File I/O vs SQL.

I agree with IBM. SQL is better than file I/O. File I/O might be a tad
faster but you need to look beyond just the one file to one file
comparisons.

File I/O is faster but what happens when you start building access plans
and join multiple tables. You also have pseudo closes to consider. In File
I/O and you close a file, it is closed. In SQL, SQL will only partly close
the file. The next time you open, it has it ready to open again.

The big problem I have seen with SQL is that people try to use SQL as File
I/O, just doing a one to one replacement. I see all the time people doing a
Select * (Shudder) in a loop and then saying if this value is something
loop back and then check another value and loop back instead of using the
database engine.

The other thing I see is read in a loop and then issue another Select * for
another table. Again not joining and letting the database engine do the
work. In other words, building a data base access plan.

The one big issue I have seen with SQL is un-normalized tables. (Array of
values, etc). SQL does not like un-normalized tables.

2. Encapsulating

As I said, our company has just gone through a whole spat of this. Results
in my opinion have not been good.

My basic point with SQL is that SQL is designed to give you a logical view
of the data vs a physical view. IBM has spent millions of dollars making
all this work efficiently. The second you start encapsulating SQL you start
eliminating that. Now you have to have a single file, you have to read the
entire record, You either have to prepare the SQL statement every time or
you have to make what you access fixed which pretty much says you have to
prepare the statement every time but that opens up performance issues.

My personal opinion is that you should use SQL as SQL. Don't write SQL as
File I/O. Build an access plan(What do I need, when do I need it) and
implement as few statements as possible. Bring in only what you need.

Make sure you prototype and run in Visual Explain to see how it will be
executed.

My other point is encapsulate functions, not SQL. Build functions in
service programs to perform business logic with SQL to access the database.

Anyway my opinions only.

On Thu, Sep 15, 2016 at 10:33 AM, Joni Vanderheijden <
joni_vanderheijden@xxxxxxxxxxx> wrote:

Hello everyone,


I was wondering if you could share your thoughts on an idea we've been
pondering on. I apologise if this idea was discussed before, I'm pretty new
to the mailing lists.


A colleague of mine proposed to create a service program for each new
table, implementing the CRUD functionalities in SQL. We would be using the
functions from the service program to access the files instead of native
RPG I/O.


The arguments are that IBM is focusing its improvements on SQL, and
leaving native I/O behind. Also this would separate data access from
business logic and in time create a database layer. An other solid argument
is that we would limit the need for creating indexes, and could construct
the appropriate access paths when performance is evaluated.


However, I have some doubts for this approach. I fear that it would
discourage developers to create programs starting from a joined SQL
statements (program join), instead encourage sequential read operations
from multiple files (program join). I feel this is where most of the
performance gains can be found. Additionally, I've always thought that
native I/O is more performant in retrieving a small amount of records from
a file, so I thought the service programs could lead to deterioration of
performance.


Any feedback on these ideas? Maybe experience in implementing something
similar? Or some documentation?


Thanks in advance,

Joni


--
This is the RPG programming on the IBM i (AS/400 and iSeries) (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.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.