Difference between revisions of "Information Systems:Optimizing ENDOFDAY / overnight processing"

Revision as of 14:47, 14 December 2017

Investigating ways to shorten the total time of the overnight process (i.e. backup, ASW processing, rebuilds etc.)

Potential avenues to explore

Excluding objects from the temporary backup

Currently, the temporary backup job saves the entirety of the following libraries:

RCSUTILPRD
UP1480BFVA
UWDASWPRDD
WEBPRDD

The backup currently takes about 1h10m, having grown from an average of ~50m at the start of 2016. Given the libraries above, the increase in backup time can most likely be attributed to organic data growth. While the philosophy of the temporary backup (e.g. what dangers we're protecting against by doing it) should be questioned overall, an immediate fix would be to logically assemble a list of files to exclude from each of the libraries above.

Excluding objects from the full backup

A full system save is cute, and the original times of ~2h drew no objections. But the entire backup will also start to grow

XXSTATSP rebuild

XXSTATSP is a file that gets rebuilt every night, and part of this rebuild is a routine to compile sales statistics at the item-warehouse level for the past 12 months. To get this information, the rebuild uses Analyzer (balance) files. These balance files were rebuilt in mid-2017 to include data from more years (this was to fix the infamous 32.94 issue). Therefore, ANOBAL is now larger than before (probably 20% larger). The effect of this growth on the rebuild time of XXSTATSP is exponentially amplified by the program's inefficient design, which essentially does the following:

Take each item in XXITEMP.
For each item, iterate through the entirety of ANOBAL, filtering by current year
Get sales in $.
Iterate through ANOBAL again to get quantity in number of units.

By using SQL and possibly creating a work table (a subset of ANOBAL that serves the purpose of the rebuild), this process could potentially be improved. At best, this rebuild took 45m. After the Analyzer rebuild and organic growth, the process now takes a whopping 1h35m.

BLDCSTSL2

As with the XXSTATS rebuild, BLDCSTSL2 also looks at Analyzer/ANOBAL. This process has grown from an average of 14-16m to 25-30m.

XXITEMP rebuild

The rebuild of XXITEMP has been consistent at 15-20m. However, at the beginning of every month, there is additional processing (to be examined) that extends this rebuild to 40m+.

Other notes

It is startling how fast some processes in the overnight are. Consider DIS TO FIN, which we consider some big, critical task that allows ASW to 'go into tomorrow'. It takes less than a minute.

Methods of Analysis

A Python script was developed to run over the info in RCSUTILPRD/LGFITXP, which holds the data for the logging utility developed by Steel Net and used extensively in the overnight processing jobs to log each step. The logging only became consistent in Mar. 2016 after Sheila settled on the overnight scheme that still persists today.

@@ Line 1: / Line 1: @@
+Investigating ways to shorten the total time of the overnight process (i.e. backup, ASW processing, rebuilds etc.)
+==Potential avenues to explore==
+===Excluding objects from the temporary backup===
+Currently, the temporary backup job saves the entirety of the following libraries:
+* RCSUTILPRD
+* UP1480BFVA
+* UWDASWPRDD
+* WEBPRDD
+The backup currently takes about 1h10m, having grown from an average of ~50m at the start of 2016. Given the libraries above, the increase in backup time can most likely be attributed to organic data growth. While the philosophy of the temporary backup (e.g. what dangers we're protecting against by doing it) should be questioned overall, an immediate fix would be to logically assemble a list of files to exclude from each of the libraries above.
+===Excluding objects from the full backup===
+A full system save is cute, and the original times of ~2h drew no objections. But the entire backup will also start to grow
+===XXSTATSP rebuild===
+XXSTATSP is a file that gets rebuilt every night, and part of this rebuild is a routine to compile sales statistics at the item-warehouse level for the past 12 months. To get this information, the rebuild uses Analyzer (balance) files. These balance files were rebuilt in mid-2017 to include data from more years (this was to fix the infamous 32.94 issue). Therefore, ANOBAL is now larger than before (probably 20% larger). The effect of this growth on the rebuild time of XXSTATSP is exponentially amplified by the program's inefficient design, which essentially does the following:
+* Take each item in XXITEMP.
+* For each item, iterate through the entirety of ANOBAL, filtering by current year
+* Get sales in $.
+* Iterate through ANOBAL again to get quantity in number of units.
+By using SQL and possibly creating a work table (a subset of ANOBAL that serves the purpose of the rebuild), this process could potentially be improved. At best, this rebuild took 45m. After the Analyzer rebuild and organic growth, the process now takes a whopping 1h35m.
+====BLDCSTSL2====
+As with the XXSTATS rebuild, BLDCSTSL2 also looks at Analyzer/ANOBAL. This process has grown from an average of 14-16m to 25-30m.
+===XXITEMP rebuild===
+The rebuild of XXITEMP has been consistent at 15-20m. However, at the beginning of every month, there is additional processing (to be examined) that extends this rebuild to 40m+.
+==Other notes==
+* It is startling how fast some processes in the overnight are. Consider DIS TO FIN, which we consider some big, critical task that allows ASW to 'go into tomorrow'. It takes less than a minute.
+===Methods of Analysis===
+A Python script was developed to run over the info in RCSUTILPRD/LGFITXP, which holds the data for the logging utility developed by Steel Net and used extensively in the overnight processing jobs to log each step. The logging only became consistent in Mar. 2016 after Sheila settled on the overnight scheme that still persists today.
 [[Category: Overnight Processing]]