RE: SQL for performance (overnight batch jobs) -- MIDRANGE-L

Rick, that's a great improvement. I certainly don't want to sound like you should not do the SQL. You have a large system, many processors, probably SMP is installed, so there is great benefit. If it was IO bound, which it sounds like, then this looks like a good solution. If you were stuck with normal IO, you could get similar results with various helper jobs, bringing in a different record range in each one.

Regards, and thanks for the further info.

Vern

At 03:30 PM 1/9/2003 -0600, you wrote:

Vern,

A PEX trace was run on our batch process and some issues with the current
application came to light.  We are working on addressing them in their RPG,
CL and DDS formats.  After almost every issue identified was a long term
recommendation that involved SQL.  This is the main reason for the interest
in SQL.  It didn't help that I converted an RPG process that was running for
3 days (no that's not a typo and yes some of the RPG code was inefficient)
to a single SQL statement that runs in 6 - 8 hours.  Our manager is pretty
sold on the idea of moving to SQL for the database design and access.  I
wanted to get a head start on identifying some of the things we might
encounter along the way.

Rick

 -----Original Message-----
From:   Vern Hamberg [mailto:vhamberg@centerfieldtechnology.com]
Sent:   Thursday, January 09, 2003 1:37 PM
To:     Midrange Systems Technical Discussion
Subject:        RE: SQL for performance (overnight batch jobs)

Reeve, I agree. Recommending SQL as one-size-fits-all solution for
performance is wrong-headed, IMO. It depends--on the scope of the change,
on whether logic is completely rewritten, as Bruce (I think) alluded to in
his successful example, on other things.

The parallelism advantage only works if you have multiple processors with
SMP installed. So it depends.

The IO paging advantages that IBM mentions will not occur unless access is
very sequential (or memory is very large relative to the amount of data to
be read in) - Expert Cache (*CALC for paging) determines IO request sizes,
and very random access had better not do much more than the 4k or 16k
pages. *FIXED will not do larger than 16k IO requests, AFAIK.

My boss, a former IBMer heavily involved in database, has said that you may
gain 20% in one area, only to lose 40% in another. You've got to be careful
with these blanket recommendations.

These recommendations might be just right for the original posting party.
We don't know enough about the situation. But a good PEX profile trace
could identify bottlenecks in RPG code and point at ways to improve things
without throwing the whole thing over and starting fresh. Or maybe they are
recommending a middle ground.

Regards

Vern