|
TPAS Performance
The TPAS Logic module (TPAS_CALC_TRNSFR_PRC) was facing performance
problems. This module is a callable program which calculates the
transfer price to be used for a part number, given the ship from and
ship to area codes.
It was confirmed that the SQL Statements executed as part of the code
were using the indices defined on the various tables and that that this
was not a cause for the problem faced on site.
The program first converts the Stockroom number/Plant Mnemonic to a
valid transfer area code or validates the input transfer area code,
depending on the value of the shipping data logical. This is done by
calling a cobol program "TPAS_STKRM_TO_AREA". The part number is then
validated by checking the MPF Database on ACADMY/the AMPS file at the
site. Once the data is found to be valid, the Pricing/Discount tables
on the TPAS database are read to get the appropriate information for
the transfer price calculation.
The program was analyzed using the VAX Performance and Coverage
Analyzer.
It was found that the most time was consumed during the SET
TRANSACTION Statement executed in the TPAS_STKRM_TO_AREA module. It was
also noticed that the TPAS_STKRM_TO_AREA module detached from the
databases after the validation was complete. The next most time was
consumed by the module to fetch information from the Pricing/Discount
Tables. Further, the databases were detached from before exiting from
the callable module.
The first change was made to ensure that instead of using the SET
TRANSACTION Statement, a DECLARE TRANSACTION Statement was used. This
was expected to reduce the time somewhat. When PCA was run on the
modified version, the most time was spent on the first executable SQL
Statement. This confirmed that the greatest bottleneck was that the
databases were being detached from at the end of each call of
TPAS_CALC_TRNSFR_PRC and that this was wholly unnecessary.
The FINISH Statement found to be executed in all the modules was
replaced with a COMMIT statement, to ensure that the transaction was
not left outstanding. When the performance was measured again using
PCA, a drastic improvement in the performance was found.
It is to be noted that the program never explicitly detaches from the
database, but when the calling program ends and the image is
deactivated, all files to which the process was attached are detached
from. This could be a problem if the calling program could be left
running for a long period of time. The only real dangers are that
database maintenance activities could be affected and max attaches
threshold may be reached earlier than otherwise expected. To guard
against this potential problem, it is recommended that the next release
of this module have the users detach from the database using a separate
TPAS call when they have completed all activity related to the
calculation of the transfer prices. This should also be kept in mind
when developing other modules of the TPAS System as well as any other
system developed by us.
|