Performance tuning in Teradata

Database

Performance tuning in Teradata

Hello,

The query below has a very high PJI and i need to tune this query. Can someone please help.

SELECT DISTINCT 0 AS c1,
D1.c3 AS c2,
D1.c4 AS c3,
D1.c5 AS c4,
D1.c6 AS c5,
D1.c7 AS c6,
D1.c2 AS c7,
D1.c1 AS c8
FROM (
SELECT COUNT ( DISTINCT T203289."Subscription_Id" ) AS c1,
T203289."Source_Key" AS c2,

CASE T202846."Access_Method_Type"
WHEN 'MOBILE_NR' THEN '06' || ( COALESCE( T202846."Access_Method_Mobile" , '' ) )
WHEN 'FIXED_NR' THEN T202846."Access_Method_Fixed"
WHEN 'CAP_ID' THEN T202846."Access_Method_Cap_Id" ELSE 'Geen'
END AS c3,
T202846."Nr_Type" AS c4,
T202604."Product_Group" AS c5,
T202604."Product_name" AS c6,
T202604."Product_Subgroup" AS c7
FROM "AKB_PRD"."V_DM_F_ACCS_METH_SUBSCR" T202846,
"AKB_PRD"."V_DM_D_LOCATION" T202635,
"AKB_PRD"."V_DM_D_PRODUCT_PORTFOLIO" T202604,
"AKB_PRD"."V_DM_D_UNIFIED_CUST" T203110,
"AKB_PRD"."V_DM_D_PORTFOLIO" T201996,
"AKB_PRD"."V_DM_F_INST_BASE_CNTR_DTL" T203289
WHERE ( T202635."Location_Id" = T203289."Location_Id"
AND T202604."Product_Id" = T203289."Product_Id"
AND T202846."Source_Key" = T203289."Source_Key"
AND T201996."PORTFOLIO_ID" = T203289."Portfolio_Id"
AND T202846."Customer_Party_Id" = T203289."Customer_Party_Id"
AND T202846."Subscription_Desc" = T203289."Subscription_Desc"
AND T201996."PORTFOLIO_DISPLAY_NAME" = 'KPN Mobiel'
AND T202604."Product_Group" = 'Price'
AND T202604."Product_name" = 'Zakelijk Mobiel Internet EU'
AND T202635."House_Nr" = '5' AND T202635."ZipCode" = '1066JR'
AND T203110."Customer_Party_Id" = T203289."Customer_Party_Id"
AND T203110."UCM_Customer_Id" = '1-4C2I-3777'
AND T203110."Name" = 'PWCZM CS IOS SDU OUTS1 CU'
AND T202846."Nr_Type" <> 'Nummerbloknummer'
AND T202604."Product_Subgroup" IS NULL
AND ( T203110."Customer_Status_AKB" = 'ACTIVE' OR T203110."Customer_Status_AKB" = 'UNKNOWN' )
AND T202635."House_Nr_Ext" IS NULL )
GROUP BY T202604."Product_Group",
T202604."Product_Subgroup",
T202604."Product_name",
T202846."Nr_Type",
T203289."Source_Key",

CASE T202846."Access_Method_Type"
WHEN 'MOBILE_NR' THEN '06' || ( COALESCE( T202846."Access_Method_Mobile" , '' ) )
WHEN 'FIXED_NR' THEN T202846."Access_Method_Fixed"
WHEN 'CAP_ID' THEN T202846."Access_Method_Cap_Id" ELSE 'Geen'
END ) D1;

Thanks in advance!

1 REPLY
Teradata Employee

Re: Performance tuning in Teradata

Couple of thoughts:

1.Did u Checkded the Stats on the joining columns properly.

2.Did U checked the Skewness of the query

3.i can see the use of COALESCE Function on the joining condition .Please verify it once whetehr it is mandatory or not.you check it in the Table DDL if it is defined as NOT Null then there is no point of using the COALESCE Function.

 

Please let me know if you are still looking for the solution.