Generic prompt screen at a large department store
chain
We had a prompt screen in our purchase order system
that had 15 fields on it that could be entered by a user. It was a very flexible way of listing
purchase orders and the items on them. Unfortunately, it was also the #1 online transaction in cpu
usage and used 3 times as much as the #2 transaction.
To build the list for the screen, this program had
up to five SQL cursors it opened and processed. If the screen wasn't filled after the second one,
things got ugly. If the 3rd one didn't fill the screen, it got really ugly. I researched all of
the different fields on the prompt screen and found that there were 32 valid combinations of fields.
This is a lot less than 15! (for the math geeks) or 15x14x13x12x11x10x9x8x7x6x5x4x3x2x1, which is
1,307,674,000,000 possible combinations... I wrote 32 SQL cursors for the 32 combinations,
opened, fetched & closed the appropriate ones and fetched the data into the same work fields in all of
them, so nothing had to change in the rest of the program. At that time, our mainframe was running at
88% of capacity from 8a-5p weekdays. This one change dropped our utilization to 72%. That is 16%
of a multi-million dollar mainframe. And our #1 transaction in cpu usage dropped out of the top
20.
~~~~~
|