More Tales from the Rockface
by Robert Allen Green
Long Reach Manufacturing
The following is an indepth overview of my most recent Suprtool accomplishment, here at Long Reach Manufacturing.
After having re-written a query application (that took 2 hours, 18 minutes to complete), into Suprtool (that took only 8 minutes to complete), my company decided to put me (and Suprtool) to the test once again.
Management wanted improvements in the turn-around time on producing the two monthly Sales Shipment Reports. These were consuming an enormous amount of time and overloading the system when run during the prime work hours of 7 am to 6 pm. They also wanted to have the reports generated on a more frequent basis, rather than only once a month.
The Old Way:
- Job was processed only once a month (due to length of processing time).
- Job could only process one month at a time.
- Data selected for job could only be selected based on one (1) date field, which was cumbersome.
- Job accessed four databases and four datasets.
- Program was written in Quiz, and had several hard coded tables, which led to potential problems (if or when the table was not changed in every location in the Quiz program).
- Program was not attaching the proper "suffixes" to several of the charge codes for accurate reporting and control breaks, due to the fact that the tables in the program were not all updated with the latest charge codes.
The extract from all the data bases and necessary manipulation of the data took the following elapsed time:
- During prime work time: 3 to 4 hours (an emergency run)
- During the middle of night: 2 to 3 hours (no one on machine, minimal job usage)
So.... the challenge was even more challenging!!!
My Plan of Attack:
Since Suprtool has the capability of interfacing an output file into a Quiz program (after a Quiz subfile has been created), I intended to re-use the report portion of the original Quiz program, so I did not have to write a new Cobol or Suprtool program for the report.From the original Quiz program, I stripped out over 2,300 lines of code, which consisted of all the various extractions, compares, computations, etc., and created a new Quiz program consisting of only the report portion of the Quiz program, that would eventually read in my new Suprtool-generated subfile.
After analyzing the original Quiz program, I decided to create a data file to store the various hard-coded tables that were in the original Quiz program, thus allowing for easier maintainability when it came time to change, add or delete a particular table entry. Then, in those Suprtool segments where a table was used multiple times, I was able to take advantage of the "HOLD" statement, for a future segment, thus not having to re-code those table statements.
To allow the selection of data on more than one type of date, I coded my Suprtool program to allow for the selection of either ACCT-DATE (accounting date) or ACT-SHP-DATE (actual ship date). In both of these date selections I allowed for the selection of a from and to date, which could be the same or different. This allowed the option of running the reports for more than a one month period, and by more than one type of date.
The Suprtool portion still accessed the four databases as before, but with fast serial access. I also accessed those databases, and the Suprtool Self-describing files that I created, by the most significant field used (rather than the least significant field), thus expediting any of the selection processes, and this saved time overall.
After all my Suprtool extract routines were completely written, testing of the new Sales Shipment Report began. Suprtool version 4.4 became available just as I started testing, but I decided to test what I had written, and look into improving my code with v4.4 later.
Test Results / Live Production Results
Test #1:
Total time using Suprtool was cut to 39 minutes from the
original 4 hours.
After my first test, when Robelle released version 4.4 of Suprtool, I felt the new features might reduce the 39 minutes even further. Using the new string and trim functions, I was able to combine several steps of my Suprtool program into one operation, thus reducing the extract processing time.
Test #2:
Total time using Suprtool and incorporating the new string and trim
functions was cut to 24 minutes
Wanting to reduce that 24 minutes even further, I carefully analyzed the running of the existing Quiz application, and realized that most of the processing was taking place within the same group and on the same ldev. I then directed (by hard coding) my input from one group/ldev to a different group/ldev, to try and pick up more i/o speed, and the results were as follows:
Test #3:
Final test/production with all the Suprtool enhancements and additional
fine tuning: 16 minutes from the original Quiz extract portion of 4 hours.
All of my testing so far was done during prime work hours of 7 am to 4 pm. When I tested this same procedure during off hours at midnight, my Suprtool routine was cut from 16 minutes down to a low of 10 minutes.
During my final test an opportunity arose when management needed an emergency run for November and December 2000. Running two months the old way required running the job two times. During the prime day shift, this would have taken in excess of 8 hours. I ran the job the new Suprtool way, and the total amount of time to run two months of last year (consisting of 10,782 extracted records), was an unbelievable 22 minutes.
The user was stunned when I told him his reports were ready (25 minutes later).
Long Reach is certainly happy with how Suprtool has performed and they are having me look into replacing as many Query and Quiz jobs with Suprtool routines, as quickly as possible.
Now, rather than running this job only once a month, and only during non-peak hours, this application can be run as frequently as necessary, and during prime hours.
In Conclusion:
Certainly, credit must be given where credit is due, and without the help and guidance from Robelle Sr. Technical Analyst Hans Hendriks in the area of creating and populating a Quiz subfile for my new Suprtool routine, I certainly would not have been as successful in this latest project, as I was.
Thank you Hans, and thank you, Robelle for a great product. Keep up the great improvements . . . . . . .
Robert Allen Green
Corporate Information Systems Analyst
Read Robert's original Suprtool Speed Achievement.
Read how Suprtool Eases MANMAN Month-end.
Read how Suprtool Boosts MANMAN Performance.