Re: Multiple INCLUDE statements in SQLRPGLE

On Sun, May 10, 2020 at 12:35 AM Reeve <rfritchman@xxxxxxxxx> wrote:

The copy books contain common code used in more than 50 places. It's not
confusing when you're consistent and I'm very consistent! If I have to
change the core components of the SQL, I'd like to do it once, in one
place. WRT "confusing"--absolutely not! I'm merely following best
practices by reusing code (it just happens to be source, not object).
Every time you reference an externally-defined file or execute a CALL,
you're reusing code. The fact the reuse is by the compiler is irrelevant.
But nested INCLUDES wouldn't be something I'd try--that could make the code
harder to navigate. EXEC IMMEDIATE isn't an option because I'm running
multiple queries for every transaction.

I'll second the opinion that "source code re-use" is neither a best
practice nor the same as "code re-use" through a compiled object.

/INCLUDE files should never have executable statements in them.

The IBM i provides *SRVPGM, SQL Stored Procedures, SQL User Defined
(table) functions and as a last resort, a bind-by-copy modules to enable
code re-use.

The original logic used an SQL stored procedure. But the nature of this
extremely complex pricing application caused that SP to be called millions
(as counted by performance tools) of times a day. A major increase in the
number of records in the database caused performance to tank so I swapped
out the SQL SP for a service program SP. That helped a bit but I still had
a problem so I tested a WHERE EXISTS clause in an attempt to push as much
of the selection load as possible into the database engine. While the
performance in that approach was substantially better, I wondered if I
could improve a bit more.

The original solution was the better option. If you'd like to show the
code used by either the SQL or RPG procedure, we can probably suggest
changes that would help.

One suggestion I saw mentioned, that you discarded, was the use of EXECUTE
IMMEDIATE. More generically, you should consider the use of dynamic SQL in
place of a complex static SQL.

In other words, while static SQL is faster than dynamic SQL, that only
applies when executing the exact same statement.

If you can take a complex static statement, with lots of "optional"
conditions in the WHEN or ORDER by clause and replace it by a simpler
dynamic statement, the dynamic SQL will have better performance than the
static SQL.

There's no place static SQL can be used that dynamic SQL can't.

Charles