Cumulative Sum Performance Improvement Question

Database
Enthusiast

Cumulative Sum Performance Improvement Question

Hello Experts,

 

My target table (application requirement):

CREATE VOLATILE TABLE SOM_TEST_TARGET
(
WK VARCHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC
, PRD VARCHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC
, QTR VARCHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC
, YR VARCHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC
, CUST_ATTR VARCHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC
, GEO_ATTR VARCHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC
, PROD_ATTR VARCHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC
, MEAS1_ACTL_AMT DECIMAL(24,6)
, MEAS2_ACTL_AMT DECIMAL(24,6)
, MEAS3_ACTL_AMT DECIMAL(24,6)
, MEAS4_ACTL_AMT DECIMAL(24,6)
, MEAS1_ACTL_AMT_PTD DECIMAL(38,6)
, MEAS2_ACTL_AMT_PTD DECIMAL(38,6)
, MEAS3_ACTL_AMT_PTD DECIMAL(38,6)
, MEAS4_ACTL_AMT_PTD DECIMAL(38,6)
, MEAS1_ACTL_AMT_QTD DECIMAL(38,6)
, MEAS2_ACTL_AMT_QTD DECIMAL(38,6)
, MEAS3_ACTL_AMT_QTD DECIMAL(38,6)
, MEAS4_ACTL_AMT_QTD DECIMAL(38,6)
, MEAS1_ACTL_AMT_YTD DECIMAL(38,6)
, MEAS2_ACTL_AMT_YTD DECIMAL(38,6)
, MEAS3_ACTL_AMT_YTD DECIMAL(38,6)
, MEAS4_ACTL_AMT_YTD DECIMAL(38,6)
, MEAS1_ACTL_AMT_YAG DECIMAL(24,6) -- YAG stands for Year AGo
, MEAS2_ACTL_AMT_YAG DECIMAL(24,6)
, MEAS3_ACTL_AMT_YAG DECIMAL(24,6)
, MEAS4_ACTL_AMT_YAG DECIMAL(24,6)
, MEAS1_ACTL_AMT_YAG_PTD DECIMAL(38,6)
, MEAS2_ACTL_AMT_YAG_PTD DECIMAL(38,6)
, MEAS3_ACTL_AMT_YAG_PTD DECIMAL(38,6)
, MEAS4_ACTL_AMT_YAG_PTD DECIMAL(38,6)
, MEAS1_ACTL_AMT_YAG_QTD DECIMAL(38,6)
, MEAS2_ACTL_AMT_YAG_QTD DECIMAL(38,6)
, MEAS3_ACTL_AMT_YAG_QTD DECIMAL(38,6)
, MEAS4_ACTL_AMT_YAG_QTD DECIMAL(38,6)
, MEAS1_ACTL_AMT_YAG_YTD DECIMAL(38,6)
, MEAS2_ACTL_AMT_YAG_YTD DECIMAL(38,6)
, MEAS3_ACTL_AMT_YAG_YTD DECIMAL(38,6)
, MEAS4_ACTL_AMT_YAG_YTD DECIMAL(38,6)
)
PRIMARY INDEX (WK, PRD, YR, CUST_ATTR, GEO_ATTR, PROD_ATTR)
ON COMMIT PRESERVE ROWS;

My Source table:

 

CREATE VOLATILE TABLE ACTL_YAG_MSRS
(
WK VARCHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC
, PRD VARCHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC
, QTR VARCHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC
, YR VARCHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC
, CUST_ATTR VARCHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC
, GEO_ATTR VARCHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC
, PROD_ATTR VARCHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC
, MEAS1_ACTL_AMT DECIMAL(24,6)
, MEAS2_ACTL_AMT DECIMAL(24,6)
, MEAS3_ACTL_AMT DECIMAL(24,6)
, MEAS4_ACTL_AMT DECIMAL(24,6)
)
PRIMARY INDEX (WK, PRD, YR, CUST_ATTR, GEO_ATTR, PROD_ATTR)
ON COMMIT PRESERVE ROWS;

Target table contains only last 52 weeks data. Source table contains last 7 years data. For every week, my source has about 5 million records.

 

Now, my objective is to run a job every week to refresh the target table for last 52 weeks (truncate and load every time).

 

I am calculating:

PTD as:

SUM(MEAS1_ACTL_AMT) OVER (PARTITION BY PRD, QTR, YR, CUST_ATTR, GEO_ATTR, PROD_ATTR) AS MEAS1_ACTL_AMT_PTD

QTD as:

SUM(MEAS1_ACTL_AMT) OVER (PARTITION BY QTR, YR, CUST_ATTR, GEO_ATTR, PROD_ATTR) AS MEAS1_ACTL_AMT_QTD

YTD as:

SUM(MEAS1_ACTL_AMT) OVER (PARTITION BY YR, CUST_ATTR, GEO_ATTR, PROD_ATTR) AS MEAS1_ACTL_AMT_YTD

 

I created multiple volatile tables in between to load PTD data, then QTD data, then YTD data for one measure at a time. While checking the performance of individual queries; spool, CPU SKEW, PJI are all in control.

 

However, each week data takes me about 30 mins to load. Hence, for 52 weeks, as you can imagine it would take about 26 hours which isn't great.

If I combine all the sums at once, it exceeds all performance metric thresholds and runs out of spool.

Any ideas how to improve the timings without affecting other performance metrics? Can I do the sum in a different way? Thanks in advance!

1 REPLY

Re: Cumulative Sum Performance Improvement Question

As your source table is huge, range-partitioning should have improved the performance.

Long time since you've posted this question, so you can share the approach you'd have picked then.