SAP Business Objects run on Vertica takes longer than Oracle.
JeansB
✭
Due to comparison on SAP Business Objects run on Vertica takes longer than Oracle.
Do we have a way to tuning SAP Business Objects Platform 4.1 Support Pack 4 run on Vertica faster than Oracle?
BI Server:
SAP Business Objects Platform 4.1 Support Pack 4
Windows Server 2012 R2
Connection1 - Oracle JDBC 11, Resultset = 987,993
Elapse Time 1 = 39 sec
Elapse Time 2 = 39 sec
Elapse Time 3 = 39 sec
Elapse Time 4 = 37 sec
Elapse Time 5 = 42 sec
Connection2 - Vertica 9 JDBC, Resultset = 987981
Elapse Time 1 = 54 sec
Elapse Time 2 = 60 sec
Elapse Time 3 = 59 sec
Elapse Time 4 = 53 sec
Elapse Time 5 = 59 sec
Oracle Database Server:
Oracle Version 11.2.0.4
Linux Redhat 6.6 (64bit)
_Vertica Database Server:
_
Vertica Community Edition 9.2.1-0
Linux Redhat 7.4 (64bit)
We have joined 4 tables as following query
SELECT
TABLE2_DIM.ARTICLE_NAME_TH,
TABLE3_DIM.SLS_OFC_DESC,
TABLE3_DIM.SLS_GRP_DESC,
TABLE3_DIM.SLS_ORG_DESC,
sum(TABLE1_FACT.NET_INC_TAX),
sum(TABLE1_FACT.NET_NO_TAX),
sum(TABLE1_FACT.COST_AMT),
sum(TABLE1_FACT.SALE_QTY),
sum(TABLE1_FACT.SALE_SQM),
TABLE2_DIM.ARTICLE_ID,
TABLE2_DIM.ARTICLE_TYPE_DESC,
TABLE2_DIM.ASSMT_DESC,
TABLE2_DIM.CAT_GRP_DESC,
TABLE2_DIM.PROD_GRP_DESC,
TABLE2_DIM.PROD_TYPE_DESC,
TABLE2_DIM.BRAND,
TABLE2_DIM.SUPP_SRC,
TABLE2_DIM.BASE_UOM,
TABLE1_DIM.YR_EN,
TABLE1_DIM.MTH_S_EN,
TABLE1_DIM.QTR_NAME
FROM
test.TABLE1_DIM RIGHT OUTER JOIN test.TABLE1_FACT ON (TABLE1_FACT.TIME_DATE=TABLE1_DIM.DATE_ID)
LEFT OUTER JOIN test.TABLE2_DIM ON (TABLE2_DIM.SK_ARTICLE_ID=TABLE1_FACT.SK_ARTICLE_ID)
LEFT OUTER JOIN test.TABLE3_DIM ON (TABLE1_FACT.SLS_AREA_ID=TABLE3_DIM.SLS_AREA_ID)
WHERE
TABLE1_FACT.TIME_DATE BETWEEN '2019-01-01' AND '2019-07-31'
GROUP BY
1, 2, 3, 4, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21;
On Vertica all dimension tables have postfix is _DIM are set to UNSEGMENTED and TABLE1_FACT is SEGMENTED by hash ().
Explain Plan:
+-GROUPBY HASH (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GROUPS) [Cost: 11M, Rows: 3M] (PATH ID: 1)
Aggregates: sum(), sum(), sum(), sum(), sum()Group By: TABLE2_DIM.ARTICLE_NAME_TH, TABLE3_DIM.SLS_OFC_DESC, TABLE3_DIM.SLS_GRP_DESC, TABLE3_DIM.SLS_ORG_DESC, TABLE2_DIM.ARTICLE_ID, TABLE2_DIM.ARTICLE_TYPE_DESC, TABLE2_DIM.ASSMT_DESC, TABLE2_DIM.CAT_GRP_DESC, TABLE2_DIM.PROD_GRP_DESC, TABLE2_DIM.PROD_TYPE_DESC, TABLE2_DIM.BRAND, TABLE2_DIM.SUPP_SRC, TABLE2_DIM.BASE_UOM, TABLE1_DIM.YR_EN, TABLE1_DIM.MTH_S_EN, TABLE1_DIM.QTR_NAMEExecute on: All Nodes
+---> JOIN HASH [LeftOuter] [Cost: 4M, Rows: 3M] (PATH ID: 2) Outer (LOCAL ROUND ROBIN) Inner (BROADCAST)
Join Cond: (TABLE1_FACT.SLS_AREA_ID = TABLE3_DIM.SLS_AREA_ID)Execute on: All Nodes
+-- Outer -> JOIN HASH [RightOuter] [Cost: 4M, Rows: 3M] (PUSHED GROUPING) (PATH ID: 3) Outer (FILTER) Inner (RESEGMENT)
Join Cond: (TABLE2_DIM.SK_ARTICLE_ID = TABLE1_FACT.SK_ARTICLE_ID)Materialize at Input: TABLE1_FACT.SLS_AREA_ID, TABLE1_FACT.TIME_DATE, TABLE1_FACT.SALE_QTY, TABLE1_FACT.SALE_SQM, TABLE1_FACT.NET_INC_TAX, TABLE1_FACT.SK_ARTICLE_ID, TABLE1_FACT.NET_NO_TAX, TABLE1_FACT.COST_AMTExecute on: All Nodes
+-- Outer -> STORAGE ACCESS for TABLE2_DIM [Cost: 282K, Rows: 1M] (PATH ID: 4)
Projection: test.TABLE2_DIM_NEW_projMaterialize: TABLE2_DIM.SK_ARTICLE_ID, TABLE2_DIM.ARTICLE_ID, TABLE2_DIM.ARTICLE_NAME_TH, TABLE2_DIM.ARTICLE_TYPE_DESC, TABLE2_DIM.PROD_TYPE_DESC, TABLE2_DIM.PROD_GRP_DESC, TABLE2_DIM.CAT_GRP_DESC, TABLE2_DIM.ASSMT_DESC, TABLE2_DIM.BASE_UOM, TABLE2_DIM.BRAND, TABLE2_DIM.SUPP_SRCExecute on: All NodesRuntime Filter: (SIP1(HashJoin): TABLE2_DIM.SK_ARTICLE_ID)
+-- Inner -> JOIN HASH [LeftOuter] [Cost: 516K, Rows: 3M] (PUSHED GROUPING) (PATH ID: 5)
Join Cond: (TABLE1_FACT.TIME_DATE = TABLE1_DIM.DATE_ID)Execute on: All Nodes
+-- Outer -> STORAGE ACCESS for TABLE1_FACT [Cost: 516K, Rows: 3M] (PATH ID: 6)
Projection: test.TABLE1_FACT_NEW_DBD_21_seg_T6_b0Materialize: TABLE1_FACT.TIME_DATEFilter: ((TABLE1_FACT.TIME_DATE >= '2019-01-01'::date) AND (TABLE1_FACT.TIME_DATE <= '2019-07-31'::date))Execute on: All Nodes
+-- Inner -> STORAGE ACCESS for TABLE1_DIM [Cost: 12, Rows: 207] (PATH ID: 7)
Projection: test.TABLE1_DIM_NEW_projMaterialize: TABLE1_DIM.DATE_ID, TABLE1_DIM.MTH_S_EN, TABLE1_DIM.QTR_NAME, TABLE1_DIM.YR_ENFilter: ((TABLE1_DIM.DATE_ID >= '2019-01-01'::date) AND (TABLE1_DIM.DATE_ID <= '2019-07-31'::date))Execute on: All Nodes
+-- Inner -> STORAGE ACCESS for TABLE3_DIM [Cost: 70, Rows: 277] (PATH ID: 8)
Projection: test.TABLE3_DIM_proj_b0Materialize: TABLE3_DIM.SLS_AREA_ID, TABLE3_DIM.SLS_GRP_DESC, TABLE3_DIM.SLS_OFC_DESC, TABLE3_DIM.SLS_ORG_DESCExecute on: All Nodes
Answers
After upgrade SAP Business Objects Platform to 4.2 Support Pack 7, it solved our performance issue.