|
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.
As an Amazon Associate we earn from qualifying purchases.
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.