Query optimization issue

Database

Query optimization issue

Hi All, I have the given query as below. There are nested query A,B,C using same tables for joins just the entities joined are different each time. The problem is query is not optimized and does not run in database for more records. For few records gives correct result. Can somebody help me here.

SELECT * FROM
(SELECT ORG_ENTITY_ID, VERTICAL_MARKET_TOP_CD AS BRANCH_VERTICAL_MARKET_CODE, COUNT_VMTC AS VMS_TOP_CODE_COUNT
FROM
(SELECT
OE.BK_ORG_ENTITY_ID_LINT AS ORG_ENTITY_ID,
PVD.VMS_TOP_CODE AS VERTICAL_MARKET_TOP_CD,
COUNT(VERTICAL_MARKET_TOP_CD) AS COUNT_VMTC
FROM NOTOUCHPVWDB.PV_CUST_ORG_ENTITY_TV OE
INNER JOIN NOTOUCHPVWDB.PV_GLOBAL_CUST_ORG_ENTITY_TV GOE
ON OE.BK_GLOBAL_ORG_ENTITY_ID_LINT = GOE.BK_GLOBAL_ORG_ENTITY_ID_LINT
AND OE.END_TV_DT ='3500-01-01'
AND GOE.END_TV_DT = '3500-01-01'
AND OE.BK_ORG_ENTITY_ID_LINT = 3000071086
INNER JOIN NOTOUCHPVWDB.PV_ORG_ENTITY_CUST_PRTY_LNK_TV OELNK
ON OE.BK_ORG_ENTITY_ID_LINT = OELNK.BK_ORG_ENTITY_ID_LINT
AND OELNK.END_TV_DT = '3500-01-01'
INNER JOIN NOTOUCHPVWDB.PV_PARTY_TV PTA
ON OELNK.CUSTOMER_PARTY_KEY=PTA.PARTY_KEY
AND PTA.END_TV_DATE='3500-01-01'
AND (PARTY_SSOT_PARTY_ID_INT IS NOT NULL AND PARTY_SSOT_PARTY_ID_INT > 0)
INNER JOIN NOTOUCHPVWDB.PV_MT_CUSTOMER_PARTY_HIERARCHY PMCPH
ON PMCPH.BRANCH_CUSTOMER_PARTY_KEY=PTA.PARTY_KEY
INNER JOIN NOTOUCHPVWDB.PV_VMS_DIVISION_TV PVD
on PMCPH.BRANCH_VMS_TOP_CD = PVD.VMS_TOP_CODE
AND PVD.END_TV_DATE='3500-01-01'
INNER JOIN NOTOUCHPVWDB.PV_VMS_MAJOR_GROUP_TV NVMG
ON PMCPH.BRANCH_VMS_SUBGROUP_CD = NVMG.VMS_SUBGROUP_CODE
AND NVMG.END_TV_DATE = '3500/01/01'
GROUP BY 1,2) OE
QUALIFY ROW_NUMBER() OVER (PARTITION BY ORG_ENTITY_ID ORDER BY COUNT_VMTC DESC)=1) A,
(SELECT VERTICAL_MARKET_TOP_CD AS HQ_VERTICAL_MARKET_TOP, COUNT_VMTC AS VMS_COUNT
FROM
(SELECT
OE.BK_ORG_ENTITY_ID_LINT AS ORG_ENTITY_ID,
PVD.VMS_TOP_CODE AS VERTICAL_MARKET_TOP_CD,
COUNT(VERTICAL_MARKET_TOP_CD) AS COUNT_VMTC
FROM NOTOUCHPVWDB.PV_CUST_ORG_ENTITY_TV OE
INNER JOIN NOTOUCHPVWDB.PV_GLOBAL_CUST_ORG_ENTITY_TV GOE
ON OE.BK_GLOBAL_ORG_ENTITY_ID_LINT = GOE.BK_GLOBAL_ORG_ENTITY_ID_LINT
AND OE.END_TV_DT ='3500-01-01'
AND GOE.END_TV_DT = '3500-01-01'
AND OE.BK_ORG_ENTITY_ID_LINT = 3000071086
INNER JOIN NOTOUCHPVWDB.PV_ORG_ENTITY_CUST_PRTY_LNK_TV OELNK
ON OE.BK_ORG_ENTITY_ID_LINT = OELNK.BK_ORG_ENTITY_ID_LINT
AND OELNK.END_TV_DT = '3500-01-01'
INNER JOIN NOTOUCHPVWDB.PV_PARTY_TV PTA
ON OELNK.CUSTOMER_PARTY_KEY=PTA.PARTY_KEY
AND PTA.END_TV_DATE='3500-01-01'
AND (PARTY_SSOT_PARTY_ID_INT IS NOT NULL AND PARTY_SSOT_PARTY_ID_INT > 0)
INNER JOIN NOTOUCHPVWDB.PV_MT_CUSTOMER_PARTY_HIERARCHY PMCPH
ON PMCPH.HQ_CUSTOMER_PARTY_KEY=PTA.PARTY_KEY
INNER JOIN NOTOUCHPVWDB.PV_VMS_DIVISION_TV PVD
ON PMCPH.HQ_VMS_TOP_CD = PVD.VMS_TOP_CODE
AND PVD.END_TV_DATE='3500-01-01'
INNER JOIN NOTOUCHPVWDB.PV_VMS_MAJOR_GROUP_TV NVMG
ON PMCPH.HQ_VMS_SUBGROUP_CD = NVMG.VMS_SUBGROUP_CODE
AND NVMG.END_TV_DATE = '3500/01/01'
GROUP BY 1,2) OE
QUALIFY ROW_NUMBER() OVER (PARTITION BY ORG_ENTITY_ID ORDER BY COUNT_VMTC DESC)=1) B,
(SELECT VERTICAL_MARKET_TOP_CD AS GU_TOP_CODE, COUNT_VMTC AS VMS_COUNT
FROM
(SELECT
OE.BK_ORG_ENTITY_ID_LINT AS ORG_ENTITY_ID,
PVD.VMS_TOP_CODE AS VERTICAL_MARKET_TOP_CD,
COUNT(VERTICAL_MARKET_TOP_CD) AS COUNT_VMTC
FROM NOTOUCHPVWDB.PV_CUST_ORG_ENTITY_TV OE
INNER JOIN NOTOUCHPVWDB.PV_GLOBAL_CUST_ORG_ENTITY_TV GOE
ON OE.BK_GLOBAL_ORG_ENTITY_ID_LINT = GOE.BK_GLOBAL_ORG_ENTITY_ID_LINT
AND OE.END_TV_DT ='3500-01-01'
AND GOE.END_TV_DT = '3500-01-01'
AND OE.BK_ORG_ENTITY_ID_LINT = 3000071086
INNER JOIN NOTOUCHPVWDB.PV_ORG_ENTITY_CUST_PRTY_LNK_TV OELNK
ON OE.BK_ORG_ENTITY_ID_LINT = OELNK.BK_ORG_ENTITY_ID_LINT
AND OELNK.END_TV_DT = '3500-01-01'
INNER JOIN NOTOUCHPVWDB.PV_PARTY_TV PTA
ON OELNK.CUSTOMER_PARTY_KEY=PTA.PARTY_KEY
AND PTA.END_TV_DATE='3500-01-01'
AND (PARTY_SSOT_PARTY_ID_INT IS NOT NULL AND PARTY_SSOT_PARTY_ID_INT > 0)
INNER JOIN NOTOUCHPVWDB.PV_MT_CUSTOMER_PARTY_HIERARCHY PMCPH
ON PMCPH.GU_CUSTOMER_PARTY_KEY=PTA.PARTY_KEY
INNER JOIN NOTOUCHPVWDB.PV_VMS_DIVISION_TV PVD
ON PMCPH.GU_VMS_TOP_CD = PVD.VMS_TOP_CODE
AND PVD.END_TV_DATE='3500-01-01'
INNER JOIN NOTOUCHPVWDB.PV_VMS_MAJOR_GROUP_TV NVMG
ON PMCPH.GU_VMS_SUBGROUP_CD = NVMG.VMS_SUBGROUP_CODE
AND NVMG.END_TV_DATE = '3500/01/01'
GROUP BY 1,2) OE
QUALIFY ROW_NUMBER() OVER (PARTITION BY ORG_ENTITY_ID ORDER BY COUNT_VMTC DESC)=1) C

1) I am having performance issue.If the entire query is run for one single OE ID, the query gives output.But when run for entire OE ID in system it gives CPU error.

2)What exactly I am doing in this query is,

For each OE ID there are Branch CR Party ID, HQ CR Party ID, GU CR Party ID and corresponding VMS code attached. I have to assign the top occurence VMS code to OE ID for Branch,HQ and GU.

So for OE ID

ORG_ENTITY_ID BRANCH_VERTICAL_MARKET_CODE VMS_TOP_CODE_COUNT HQ_VERTICAL_MARKET_TOP VMS_COUNT GU_TOP_CODE VMS_COUNT
3,000,071,086 RETAIL 7,027 RETAIL 7,714 RETAIL 8,087

 

The maximum VMS code for branch came out to be retail,HQ ->Retail and GU -> Retail.

Each A,B C differ in code at-->

INNER JOIN NOTOUCHPVWDB.PV_MT_CUSTOMER_PARTY_HIERARCHY PMCPH
ON PMCPH.BRANCH_CUSTOMER_PARTY_KEY=PTA.PARTY_KEY
INNER JOIN NOTOUCHPVWDB.PV_VMS_DIVISION_TV PVD
on PMCPH.BRANCH_VMS_TOP_CD = PVD.VMS_TOP_CODE
AND PVD.END_TV_DATE='3500-01-01'
INNER JOIN NOTOUCHPVWDB.PV_VMS_MAJOR_GROUP_TV NVMG
ON PMCPH.BRANCH_VMS_SUBGROUP_CD = NVMG.VMS_SUBGROUP_CODE
AND NVMG.END_TV_DATE = '3500/01/01'
2 REPLIES
Teradata Employee

Re: Query optimization issue

a simple insert select is taking 56 mins for 5m rows.

stats are collected

on doing show sel * its showing create join index on certain table and stats are not collected on that table(join index)

can this be the reason insert is taking too long..?

Junior Contributor

Re: Query optimization issue

Apparently the join-conditions on ORG_ENTITY_ID are missing and you get CROSS-joins.