Charles, thank you for your comments.
Let me reiterate the problem: a simple stored procedure performing a simple
database read can be called more than 20,000 times in a single
transaction. My goal is to make the data model look like a relational
table; today this particular feature makes it look like a hierarchical
database. Now, with more memory, more than 75% of my rate requests return
a rate in less than one second; some of the performance people
we've worked with suggested early on that we were memory-constrained. I
was wrong (too low) about the volume of transaction: we're getting more
than 100,000 rate requests in a 20-hour period and about 80% are rejected
(meaning the request must traverse the full set of business rules embodied
in the code only to return a "no rate" error).
I have pushed considerable business functions into SQL stored procedures
and functions. It's not that I don't know how to use them; it's simply a
matter of identifying the problem and applying the correct technology.
I've not encountered a use for bind-by-copy coding.
The WHERE clause has a number of selections--effective date, weight range,
type of shipment, terms code, inbound or outbound, and so on--driven by
program variables. It's a relatively uncomplicated statement; I have many
applications using far more complex selection/ordering criteria. This
business logic is used in multiple tables, and while I don't be able to
reuse the code, the final design will be reused. My testing so far
suggests replacing the stored procedure (code below) with a join will
deliver a significant performance improvement--but I'm testing my testing
to make sure I'm getting accurate results.
I'll listen to your opinions WRT code re-use but my reading of McConnell,
Martin, and Fowler suggests, without qualification, that reusing code is a
best practice. My programs use copybooks for documentation, copyright
information, prototypes, and certain low-level utility functions (date
reformatting, returning the day number from a date, handling subfile error
message); there are service programs in virtually every application program
and numerous stored procedures in my SQL. Sure, you can do most of these
easily in your own code but modern programming languages have libraries of
functions; I'm just adding my own. If a programmer has to code the same
routine twice, you've just add item to the technical debt schedule. WRT
/INCLUDES, you should never include executable code in conditional
/INCLUDES.
Forgetting the benefits of OOP architecture, object code is just source
code that's *really* hard to read. It's all reusable!
Here's the original code, which was replaced by a service program doing the
read. A later version loads table GPP005 into an array and then does a
lookup but there are potential issues with this approach.
Stay safe. Stay healthy.
-reeve
CREATE OR REPLACE FUNCTION sps010(qp_type VARCHAR(10), --Value type
(ST, GGST, ZIP)
qp_value VARCHAR(25), --Argument
qp_geogroup VARCHAR(25), --Geo group
name
qp_customer VARCHAR(7), --Geo group
customer
qp_eff DATE) --Effective
date
RETURNS CHAR(1)
LANGUAGE SQL
READS SQL DATA
SET OPTION DBGVIEW=*SOURCE
BEGIN
DECLARE return_value CHAR(1) DEFAULT '0';
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
--Customer-agnostic GG
SELECT '1'
INTO return_value
FROM gpp005
WHERE gpckey = qp_geogroup
AND (qp_eff >= gpceff AND gpccan = '0001-01-01'
OR qp_eff BETWEEN gpceff AND gpccan)
AND gpccust = ' '
AND
(
(qp_type = 'ZIP' AND qp_value between gpclow and gpchigh)
OR
(qp_type = 'ZIPS' AND qp_value between gpclow and gpchigh)
OR
(qp_type = 'ST' AND qp_value = gpclow)
OR
(qp_type = 'STATE' AND qp_value = gpclow)
OR
(qp_type = 'STATES' AND qp_value = gpclow)
OR
(qp_type = 'TSA' AND qp_value = gpclow)
OR
(qp_type = 'CUST' AND qp_value = gpclow)
)
FETCH FIRST ROW ONLY
WITH NC;
IF SQLSTATE = '00000'
THEN
RETURN return_value;
END IF;
RETURN '0';
END;
Label on function SPS010 is
'Validate geo group';
CL: chgobjd sps010 *srvpgm text('Validate geo group');
On Mon, May 11, 2020 at 8:16 AM Charles Wilt <charles.wilt@xxxxxxxxx> wrote:
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
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
midrange.com
