The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

SAP Business Objects run on Vertica takes longer than Oracle.

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

Tagged:

Answers

  • After upgrade SAP Business Objects Platform to 4.2 Support Pack 7, it solved our performance issue.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.