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.My query is as:

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

MY Data is:

ORG_ENTITY_ID BRANCH_CR_PARTY_ID PARTY_KEY BRANCH_PARTY_KEY TOP_CODE HQ_PARTY_ID HQ_TOP_CODE GU_PARTY_ID GU_TOP_CODE
1 3,000,071,086 119,944,668 51,545,564 51,545,564 RETAIL 18,152 RETAIL 2,654,954 RETAIL
2 3,000,071,086 798,728 12,189,887 12,189,887 RETAIL 18,152 RETAIL 2,654,954 RETAIL
3 3,000,071,086 1,054,812 6,878,544 6,878,544 RETAIL 42,499 RETAIL 2,654,954 RETAIL
4 3,000,071,086 452,302 1,401,712 1,401,712 RETAIL 42,499 RETAIL 2,654,954 RETAIL
5 3,000,071,086 278,722 2,199,932 2,199,932 RETAIL 18,152 RETAIL 2,654,954 RETAIL
6 3,000,071,086 46,019,546 7,407,463 7,407,463 RETAIL 2,654,954 RETAIL 2,654,954 RETAIL
7 3,000,071,086 345,823 13,063,063 13,063,063 RETAIL 42,499 RETAIL 2,654,954 RETAIL
8 3,000,071,086 509,337 9,751,143 9,751,143 RETAIL 18,152 RETAIL 2,654,954 RETAIL
9 3,000,071,086 932,115 2,713,292 2,713,292 RETAIL 18,152 RETAIL 2,654,954 RETAIL
10 3,000,071,086 457,457 14,555,484 14,555,484 RETAIL 42,499 RETAIL 2,654,954 RETAIL
11 3,000,071,086 908,334 4,156,859 4,156,859 RETAIL 28,360 RETAIL 2,654,954 RETAIL
12 3,000,071,086 10,866,922 8,329,279 8,329,279 RETAIL 2,654,954 RETAIL 2,654,954 RETAIL
13 3,000,071,086 145,591,641 79,019,405 79,019,405 RETAIL 2,654,954 RETAIL 2,654,954 RETAIL
14 3,000,071,086 991,878 5,906,933 5,906,933 RETAIL 28,360 RETAIL 2,654,954 RETAIL
15 3,000,071,086 5,398,193 11,460,807 11,460,807 RETAIL 42,499 RETAIL 2,654,954 RETAIL
16 3,000,071,086 5,398,241 11,481,406 11,481,406 RETAIL 42,499 RETAIL 2,654,954 RETAIL
17 3,000,071,086 986,507 6,563,126 6,563,126 RETAIL 18,152 RETAIL 2,654,954 RETAIL
18 3,000,071,086 1,053,459 13,447,446 13,447,446 RETAIL 42,499 RETAIL 2,654,954 RETAIL
19 3,000,071,086 5,608,642 11,658,849 11,658,849 RETAIL 3,952,516 RETAIL 2,654,954 RETAIL
20 3,000,071,086 906,433 1,542,872 1,542,872 RETAIL 220,514 RETAIL 2,654,954 RETAIL

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
Senior Apprentice

Re: Query optimization issue

How many ORG_ENTITY_IDs exist?

You're currently CROSS joining those three results, simply adding proper join-conditions should help.

Re: Query optimization issue

There are around 8920235 OE ID exist in a system.

Each OE ID is can be connected with multiple Branch CR Party ID,multiple HQ Party ID, multiple GU Party ID.

I have to find the top vertical code for each OE, depending on Branch, HQ and GU. All these entities are coming from same tables just the join is different  each time for these. So I took one at a time because we have to compute using each at a time.

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'
/////////////////////////////////////

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'
///////////////////////////////////////

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'

So that is the problem. I am novice so not getting the better logic to tweak this query for better performance.