× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Have you looked at user indexes? Sounds like it might be what you are
looking for.

On Sun, May 10, 2020, 5:20 PM Reeve <rfritchman@xxxxxxxxx> wrote:

There are about 5,000 names, 25,000 ZIP codes, and 30 pages of business
requirements driving this application. I use stored procedures extensively
throughout the program in question but significant changes in volumes and
business operations have exposed a weakness in my design. The single issue
I'm working with is performance: a typical request might require 5,000
calls to this stored procedure to check all available rates, with some
requests making many more calls. We have 10,000 invoices a day and 30,000
requests (most through pricing bots) through the internet. The stored
procedure does a simple database read but it's another read nonetheless;
that's why I'm trying to push the logic out of the SP and into the database
via JOIN's. I agree that a stored procedure is the right programming
technique in most cases but not for this extremely database-intensive
special case, where some customers have more than 70,000 rows of pricing
options--think of a from/to matrix of 275x275. Did I mention I can set
up account numbers, states, and terminals into groups using the same
mechanism? This capability greatly simplifies the pricing department's
workload--they like it too much!

I'd moved the table into memory; that made no difference. As of today,
with four cores and a memory doubling to 128 GB, with a big hunk of that
memory supporting several dozen instances of the web pricing application,
we have reasonable response time for this program (every other application
performs with subsecond response time). One other issue: the hardware
(supporting three application partitions plus the console) virtualized all
the drives. My performance experts have suggested the big production
partition should access those drives directly (physically) while leaving
the finance and development partitions virtualized. The customer is
working on that angle.

Birgitta's response makes a lot of sense and I'm going to prototype it. I
prefer to have database logic (manifesting itself as complex WHERE's and
ORDER BY's) in the database and not in the program, so this will be a step
in that direction.

/* Off-topic: Birgitta, I have learned a huge amount from your posts and
articles over the years--thank you!!! */

One other approach I'm testing: modify the stored procedure to load the
table into an array when the application initializes, do a lookup to find
the first making entry, and then iterate (no more than two or three times)
to test for effective and expiration dates. This change would be easy to
install for the short term but a medium-term consideration is an
SQL-imposed limit of between 32,751 and 32,767 entries in the array (when I
use DIM(32767) in the qualified array data structure, the precompiler
throws an error; 32,750 doesn't). Another problem is having to spoof the
array as ascending (which it will be but the compiler doesn't believe it)
so %LOOKUP doesn't have to scan all 25,000 entries before calling it
quits...but as I think about this, I've realized we'll almost aways hit an
entry. If I give up on the ascending array angle, I can load it in a
manner than pushes the most-frequently used entries to the beginning of the
array.

Stay safe, stay healthy.

Regards,
Reeve

On Sun, May 10, 2020 at 7:37 AM Niels Liisberg <nli@xxxxxxxxxxxxxxxxx>
wrote:

To me it looks like you will be better of by creating stored procedures
or
UDTF... This is exactly what they are therefore.

lør. 9. maj 2020 kl. 13.18 skrev Reeve <rfritchman@xxxxxxxxx>:

I'm trying to stack multiple INCLUDE's to generate static SQL. The
goal
is
to move common code out of the application source and into copy books
for
ease of development and maintenance. I have a need to include a JOIN
in
some cases, and while I could simply have two versions of a single
consolidated copy book (one with the join and one without), that
solution
doesn't appeal to me.

This is the first copy book:
EXEC SQL
SELECT cmcust INTO :cmcust FROM arp001

and this is the second:
WHERE cmstat = 'A'

and this is the third:
AND CMCLS = 'R'

...and there will be additional selections.

Here's what the code might look like:
/include qcpysrc,testcopy1
JOIN arp002 ON cmcust = nmcust
/include qcpysrc,testcopy2
/include qcpysrc,testcopy3
and cmcls = 'R' and nmtype = '2';

The source out of the preprocessor includes an /END-FREE and the
compiler
tags the "-" in column 11 as an error even when the source member
includes
a /FREE in it. The first SQL line isn't terminated so the preprocessor
is
likely not looking for an /END-FREE.
Position 11 Token - was not valid. Valid tokens: FOR USE

When I've added a /FREE statement to these source members, the error
still
appears. I've tried COPY as well; still getting errors in the
preprocessor
output. Using RPGPPOPT(*LVL2) and V7R3M0.

I'll be grateful for any ideas!

Stay safe and stay healthy.

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

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

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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.