Re: SQL data access through service program per file

Great advice from Nathan.

I agree single row CRUD I/O layers based on RPG should perform better than
single row CRUD layers based on SQL. Implementing a CRUD I/O layer is a
huge improvement for most shops, because it makes it much easier to
redesign your database to third normal form (3NF) or better. Getting your
database to 3NF is very important.

One thing I'd like to add is SET based processing designed around logical
business objects should be considered part of your end game and included in
the LATER stages of your plan. In many cases, SET based processing
improves performance, often very dramatically, and reduces code volume and
complexity versus single row processing. Numerous times I've rewritten RPG
batch programs that used single row RPG I/O to SET based SQL, and I
typically reduce run times by 70 to 90%, with a similar % decrease in code
volume and complexity.

Spend some time on SQL Server developer forums, and you'll notice a trend.
If someone asks for assistance with a single row CRUD application that
should have been designed as a SET based application, the use of single row
CRUD is very frequently discouraged, and SET based processing is
encouraged. Languages and tools for that platform have included great
support for SET based processing for a very long time, so the developers
there have made it the norm, whereas on the IBM i platform single row CRUD
processing is the norm.

Because my work load lends itself to it (transformation of large data
sets), I use 100% >>SET<< based SQL. I don't use single row SQL, and I
don't use single row RPG I/O, because I can't afford the performance
decrease that results from downgrading from SET processing to single row
anything. However, most developers are tasked with developing and
maintaining interactive applications that handle a very small amount of
data that are, or are much closer to being, appropriate for single row
processing. All work loads are not equal.

I'm not a fan of switching from single row RPG I/O to single row SQL for
the simple reasons that there's a good chance performance will decrease,
but most importantly, there's not enough ROI to justify the development
labor cost, nor can one justify the risk of breaking production for a tiny
ROI (no level checks and automatic index selection).

CRUD layers help get you to 3NF, but once you're at 3NF, SET based designs
should start to become your first choice for new development, with single
row CRUD used much less frequently than today. Learning how to think,
design, and implement in SETs carries a healthy learning curve, so try to
work on that training effort during your journey to 3NF. SET based designs
will likely heavily rely on SQL views, procedures, and UDTFs, but JSON and
XML documents are commonly used for SET based processing as well. In the
long term, you should think more in terms of business objects and
collections of business objects, and less in terms of rows in physical
tables.

If your database is already at 3NF and in great shape, then I'd focus on
designing SET based interfaces more than single row CRUD interfaces. Quite
frankly though, I doubt there are many IBM i databases out there fully
redesigned to 3NF and in great shape, so a CRUD layer is a great way to get
the database redesigned. For a database in bad shape, your primary goal is
to get to 3NF and minimize the number of places in your code that interact
with the database, so when tables are redesigned, you have a small number
of mainly CRUD procedures to change.

Mike

date: Fri, 16 Sep 2016 07:34:34 -0600

from: Nathan Andelin <nandelin@xxxxxxxxx>
subject: Re: SQL data access through service program per file

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.

For CRUD operations, have you considered wrapping RPG I/O in service
program procedures?

The arguments are that IBM is focusing its improvements on SQL, and
leaving native I/O behind.

I'd like pass along a quote from Jon Paris:

"I'm not a fan of "SQL is the answer... Now, what what the question?"

SQL was designed for set-based functionality while RPG I/O was designed for
record-level access. Both are valid use cases.

Also this would separate data access from business logic and in time
create a database layer.

Good architectural point.

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.

CRUD operations without indexes? No, I hope the fallacy of that is obvious.

However, I have some doubts for this approach.


If you have doubts about SQL being the answer to CRUD operations, you're
not alone.

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

When you need to join files, I'd recommend SQL Views and Join Logicals over
program joined operations.

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

I've done some benchmark testing where RPG op codes performed 100% - 600%
more efficiently than SQL equivalents. Testing a foreign-key RI constraint
using SETLL for example may perform 600% more efficiently than an SQL
SELECT.

I thought the service programs could lead to deterioration of performance.

Actually, I would recommend wrapping CRUD operations in I/O service
programs for the following reasons:

1. Avoids the problem of "F" specs multiplying like rabbits and having to
recompile many modules when file layouts occur.
2. Avoids multiple open-data paths when multiple programs need access to
the same files in the same job.
3. Facilitates a standard interface for I/O error handling (appropriate
error messages).
4. Facilitates the principles of loose coupling and high cohesion between
DB I/O modules and application modules.
5. Facilitates code maintenance by appropriately scoping I/O service
programs.

In summary, the idea of encapsulating CRUD operations into service programs
is good, but the idea of SQL being the answer to all I/O, is bad.

You asked about CRUD operations. Requirements for dealing with "result
sets" is a different matter, and something which SQL excels at.