RE: SQL for performance (overnight batch jobs)

Tom,

You're may be right about management thinking SQL is a magic bullet.  I want
to gather information, both positive and negative, to help the decision
making process.

Sorry about the confusion regarding solving the problem with SQL.

Rick

 -----Original Message-----
From:   Tom Jedrzejewicz @ San Pedro [mailto:TJedrzejewicz@contessa.com] 
Sent:   Friday, January 10, 2003 11:26 AM
To:     midrange-l@midrange.com
Subject:        RE: SQL for performance (overnight batch jobs)

My apologies, my intention was not to disparage.  Clearly, my
communication was not clear.

Graham wrote in a later post . . .
>> I think its perhaps easier to write awful SQL based code
>> than Native IO code.

THAT is really where I was trying to get . . . we all have to be
careful and not allow the TOOL used to accomplish a goal to overshadow
the SKILL and SMARTS of the person working the goal, or for that matter
the goal itself.  How often have we seen ERP projects fail because the
focus went away from improving the business and onto the care and
feeding of the ERP.

To answer your question . . .
>> Why do you wonder that we give credit to SQL for doing well what it
was designed to do?  

You related the story as . .  Rick used SQL to solve problem, so boss
wants to implement SQL.  You didn't say . . . Rick used SQL to solve the
problem, so boss wants Rick to solve more problems.  It seemed to me
like the boss thinks SQL solved the problem, not that Rick solved the
problem.  I think RICK solved the problem, using the best tool.

Finally, you closed with . . .
>> I don't believe SQL is a magic bullet but I am becoming more
convinced all
>> the time that it definitely has a place in my development toolbox.

I totally agree, but I got the impression that your boss DOES  believe
SQL is a magic bullet.

Regards to all!

>>> Rick.Chevalier@americredit.com 01/09/03 02:57PM >>>
Tom,

I'm trying to not let this one occurrence set the bar for future
expectations.  That is why I started this thread.  It was used merely
to
give insight into the situation here.

Over the years I have overhauled many applications without the use of
SQL
and seen dramatic improvement.  As I mentioned, the existing code was
inefficient and could have been improved.  That was my original task. 
The
more I worked on the code the more it became a selection process and
nothing
else.  There were no calculations other than to match records between
files.
This is something SQL does very well and in my opinion the single
SELECT
statement is easier to maintain than the RPG program in this case.  

Why do you wonder that we give credit to SQL for doing well what it
was
designed to do?  Not everything can be attributed to simply joining
files in
a SELECT.  I used the Ops Navigator Query Analyzer to identify the best
way
to perform the select.  As a result several new indexes were created to
try
and gain maximum efficiency.

I could have invested this same time in tuning the RPG program but it
would
only apply to that one program.  We find ourselves using SQL more and
more
here whether it is ad hoc querying or selecting and sorting for display
or
reporting purposes.  The investment I made in tuning the select for
this one
application also put in place the pieces to improve the performance in
these
other areas and save us time on an ongoing basis.

I don't believe SQL is a magic bullet but I am becoming more convinced
all
the time that it definitely has a place in my development toolbox.



 -----Original Message-----
From:   Tom Jedrzejewicz @ San Pedro [mailto:TJedrzejewicz@contessa.com]

Sent:   Thursday, January 09, 2003 4:15 PM
To:     midrange-l@midrange.com 
Subject:        RE: SQL for performance (overnight batch jobs)

In 1989 I rewrote the nightly batch update process for the law firm
which employed me.  The rewrite dramatically improved the stability
and
reliability of the process AND cut the run time by 85%, from 5-6 hours
to 45 minutes.  I am sure the RPG process you are discussing could
have
been cut to 8-10 hours through GOOD, EFFICIENT design and coding.

Every legacy system has some processes (Order Entry perhaps, or
Inventory month-end) that are poorly designed and have morphed over
the
years into poorly performing, inefficient monsters.  The fact that
both
you and your boss are so willing to credit SQL for the improvement
rather than intelligent programming makes me wonder.  Imaging if the
programmer who wrote the original program you replaced had been the
one
to do the SQL program.  Would the result have been a 90% improvement
in
processing time?

Don't allow that one process improvement to set the expectations for
what can be achieved.

>>> Rick.Chevalier@americredit.com 01/09/03 01:30PM >>>
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

At 12:27 PM 1/9/2003 -0500, you wrote:
>An important function of batch jobs (large and/or overnight) is
>providing batch control totals and other tools accountants (the fact
>that "tools" and "accountants" are contiguous in this sentence should
>not be construed as a political statement) rely on to demonstrate
system
>integrity.  In addition, it's a useful form of work management
(unless
>you like printing 35,000 invoices interactively).
>
>The revenue accounting environment in the transportation/logistics
>environment is complex: there are pricing, discounting, revenue
split,
>revenue allocation, and fuel price fluctuations driving every invoice
in
>a high-volume (100/day-20,000/day) environment.
>
>Real-time processing is the solution for just about everything but
>accounting.  Cutoff dates (period end, etc.) are an integral part of
the
>"measure and compare" nature of accountancy.
>
>I don't understand how the use of SQL could improve performance in an
>otherwise well-designed (proper use of LF's, etc.) batch application.
>SQL may offer some flexibility with complex joins (for reporting, of
>course) but that's about all I can think of.
>
>-reeve


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

THIS MESSAGE IS INTENDED ONLY FOR THE USE OF THE INDIVIDUAL OR ENTITY
TO
WHICH IT IS ADDRESSED AND MAY CONTAIN INFORMATION THAT IS PRIVILEGED,
CONFIDENTIAL AND EXEMPT FROM DISCLOSURE UNDER APPLICABLE LAW.  If the
reader
of this message is not the intended recipient, or the employee or
agent
responsible for delivering the message to the intended recipient, you
are
hereby notified that any dissemination, distribution, copying,
downloading,
storing or forwarding of this communication is prohibited.  If you
have
received this communication in error, please notify us immediately via
email
and delete the message from your computer files and/or data base. 
Thank
you.
_______________________________________________
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.
_______________________________________________
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.

THIS MESSAGE IS INTENDED ONLY FOR THE USE OF THE INDIVIDUAL OR ENTITY TO
WHICH IT IS ADDRESSED AND MAY CONTAIN INFORMATION THAT IS PRIVILEGED,
CONFIDENTIAL AND EXEMPT FROM DISCLOSURE UNDER APPLICABLE LAW.  If the reader
of this message is not the intended recipient, or the employee or agent
responsible for delivering the message to the intended recipient, you are
hereby notified that any dissemination, distribution, copying, downloading,
storing or forwarding of this communication is prohibited.  If you have
received this communication in error, please notify us immediately via email
and delete the message from your computer files and/or data base.  Thank
you.
_______________________________________________
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.