Tweet
Since reading about EVIs in these forums over the last few months, I've been on a mission to improve performance of some of our long-running jobs. I still haven't found a good candidate for an EVI but I have stumbled across some interesting stuff. One job in particular, which started out using 254 seconds of processing time is now down to using only 28 seconds. Here's what we did. A little background first. One of the things the job does, is to use our Item Ledger file to find Invoice Activity within a date range for a given Product. This is a large file (13 million records) and there is no existing LF with useful Keys to accomplish what the program needs to do. Since this Key Structure would only be used by this program, the decision was made not to create a permanent LF for this purpose. What the job did was to create a temporary LF over the Item Ledger by Product, Creation Date (descending) and Selecting only Invoice Document types. The RPG program would Read the LF, positioning the File Cursor by Product and Reading Equal on Product, look for Creation Dates within the specified range. A flag was set when a Creation Date matched the Selection criteria, so that the program could write a single work file record for that Product. There were two obvious problems. First, the program didn't use date as part of the key when positioning the file cursor. And second, once a record was found that matched the Selection Criteria, the program just continued to read every Item Ledger record for that Product, instead of Leaving the Read Loop. By correcting these two programming errors, processing time was reduced to about 200 seconds. Conclusion: The most perfect Access Path in the world isn't going to make your jobs run faster if the code doesn't take advantage of them. We've looked at several other "Problem" jobs in detail and many have similar problems. (And NO, I was not the original author) We had a hunch that the bulk of the problem was in the Index Build for the LF. There was no quantitative measurement, but by Displaying the Job Stack and waiting for it to move beyond that point into the RPG program, it seemed obvious that the bulk of the run time was spent building this index. Since all we were trying to accomplish was to find Products with Invoice Activity within a certain date range, a work file seemed like a good solution. Then the RPG pgm could be changed to not use the Temp LF and just chain to the work file by Product. We did some testing in interactive SQL to see if the work file could be created fast enough to justify the program change and we were pleasantly surprised at the result. We threw together a quick SQLRPG program that accepts a date parm and based on the date, Selects from the Item Ledger File and Inserts distinct Product numbers into the work file. After making the change to the original program to use the work file we retested. Processing time had dropped to less than 30 seconds !! Conclusion: 1) Work Files when used properly can be very good things. 2) SQL seems to be very well suited for this type of situation. Since there has been a lot of discussion about SQL vs Native I/O we wanted to test how quickly the Job would run using Native I/O to build the work file. We chose an existing Item Ledger LF Keyed by Product as my Primary File and used the Level Break to write to the Work File as long as at least one record matched the Document Type / Date Range criteria. The results were disapointing, almost 290 seconds of processing time, although I'll admit that we spent no time trying to improve that. But retrieving a relatively small subset of data from a very large file could be one situtation where SQL is significantly faster than Native I/O. Mike