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

Vern,

You're right.  My first assignment when I started here was to convert some
long running processes to use data queues to process the data in parallel.
This also provided a dramatic improvement.  

I appreciate all the information you and others have provided about this
topic.  I'm going to try and compile a summary sheet and forward it to
management so they can make a more informed decision about our future
direction.

Rick

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

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


_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo.cgi/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.