Query running for long time

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Visitor

Query running for long time


Hi,
I am trying to optimise the below query which runs for around an hour. I tried to create temp table for the dmuc_abh part, yet the query is taking more or less the same time. The joining conditions are all on the primary key columns yet everytime FTS is being performed for all the joins. Any idea how I can improve the query performance?
SELECT
scpm.SUBS_ID AS Subs_Id
, scpm.subs_primy_msisdn AS Msisdn
, scpm.Emagine_Key
, bad.acct_stat_cd AS Acct_Stat_Cd
, ahid.consumer_enterprise_ind AS Con_Ent_Segm_Cd
, scga.Cell_Site_Cd AS Most_Used_Cell_Site_Cd
, ahid.lang_cd AS Language_Cd
, CAST (NULL AS VARCHAR(100)) AS Alt_Msisdn_1_Val
, CAST (NULL AS VARCHAR(100)) AS Alt_Msisdn_2_Val
, CAST (NULL AS VARCHAR(100)) AS Alt_Msisdn_3_Val
, CAST (NULL AS DATE) AS Myvod_Reg_Dt
, CAST (NULL AS VARCHAR(1000)) AS Myvod_Reg_Email_Addr_Val
, CASE WHEN ahid.consumer_enterprise_ind NOT LIKE '%CON' THEN sadv.sub_segm_val ELSE NULL END AS Ent_Subsegm
, CAST(CASE WHEN p_s.party_subs_end_dttm = '2050-12-31 23:59:59' THEN 'Y' ELSE 'N' END AS CHAR(1) )AS Rica_Flg
, CAST (NULL AS CHAR(1)) AS Call_Contact_Allwd_Flg
, CAST (NULL AS CHAR(1)) AS Dm_Contact_Allwd_Flg
, CAST(NULL AS CHAR(1) )AS Email_Contact_Allwd_Flg
, CAST(NULL AS CHAR(1) )AS Mms_Contact_Allwd_Flg
, CAST (NULL AS CHAR(1) )AS Sms_Contact_Allwd_Flg
, ahid.party_ident AS Id_Num
, ahid.gendr_cd AS Gendr_Cd
, ahid.birth_dt AS Birth_Dt
, ahid.age AS Age
, CAST (NULL AS CHAR(1)) AS Email_Html_Flg
, COALESCE(bad.e_addrs, scpm.subs_primy_msisdn || '@xyz.com') AS Email_Addr
, CAST (NULL AS VARCHAR(100)) AS Loyalty_Membership_Nm
, CAST (NULL AS DATE ) AS Loyalty_Membership_End_Dt
, scga.mmuc_std_regn_nm AS Most_Used_Prov
, scga.mmuc_std_suburb_nm AS Most_Used_Suburb
, scga.mmuc_std_town_nm AS Most_Used_Town
, ahid.famly_nm AS Family_Nm
, ahid.given_nm AS Given_Nm
, CASE WHEN bad.loc_usage_cd = 'DQS_STR|ACC_EMAIL' THEN bad.addrs_line_1 END AS Home_Addr_Line_1
, CASE WHEN bad.loc_usage_cd = 'DQS_STR|ACC_EMAIL' THEN bad.addrs_line_2 END AS Home_Addr_Line_2
, CASE WHEN bad.loc_usage_cd = 'DQS_STR|ACC_EMAIL' THEN bad.addrs_line_3 END AS Home_Addr_Line_3
, CASE WHEN bad.loc_usage_cd = 'DQS_STR|ACC_EMAIL' THEN bad.addrs_line_4 END AS Home_Addr_Line_4
, bad.geo_area_suburb_nm AS Suburb_Nm
, bad.postal_cd AS Postal_Cd
, CASE WHEN bad.loc_usage_cd = 'DQS_STR|ACC_POSTAL' THEN bad.addrs_line_1 END AS Post_Addr_Line_1
, CASE WHEN bad.loc_usage_cd = 'DQS_STR|ACC_POSTAL' THEN bad.addrs_line_2 END AS Post_Addr_Line_2
, CASE WHEN bad.loc_usage_cd = 'DQS_STR|ACC_POSTAL' THEN bad.addrs_line_3 END AS Post_Addr_Line_3
, CASE WHEN bad.loc_usage_cd = 'DQS_STR|ACC_POSTAL' THEN bad.addrs_line_4 END AS Post_Addr_Line_4
, onyx_pltnm.Vip_Onyx_Start_Dt
, onyx_pltnm.Vip_Onyx_End_Dt
, onyx_pltnm.Vip_Platinum_Start_Dt
, onyx_pltnm.Vip_Platinum_End_Dt
, scga.dmuc_ahp_cell_site_cd AS Dmuc_Ah_Psd_Cell_Cd
, scga.dmuc_ah_psd_regn AS Dmuc_Ah_Psd_Netwk_Regn_Nm
, scga.dmuc_ah_psd_prov_nm AS Dmuc_Ah_Psd_Prov_Nm
, scga.dmuc_ah_psd_suburb AS Dmuc_Ah_Psd_Suburb_Nm
, scga.dmuc_ah_psd_town AS Dmuc_Ah_Psd_Town_Nm

, scga.dmuc_ahnp_cell_site_cd AS Dmuc_Ah_Non_Psd_Cell_Cd
, scga.dmuc_ah_non_psd_regn AS Dmuc_Ah_Non_Psd_Netwk_Regn_Nm
, scga.dmuc_ah_non_psd_prov_nm AS Dmuc_Ah_Non_Psd_Prov_Nm
, scga.dmuc_ah_non_psd_suburb AS Dmuc_Ah_Non_Psd_Suburb_Nm
, scga.dmuc_ah_non_psd_town AS Dmuc_Ah_Non_Psd_Town_Nm

, scga.dmuc_bhp_cell_site_cd AS Dmuc_Bh_Psd_Cell_Cd
, scga.dmuc_bh_psd_regn AS Dmuc_Bh_Psd_Netwk_Regn_Nm
, scga.dmuc_bh_psd_prov_nm AS Dmuc_Bh_Psd_Prov_Nm
, scga.dmuc_bh_psd_suburb AS Dmuc_Bh_Psd_Suburb_Nm
, scga.dmuc_bh_psd_town AS Dmuc_Bh_Psd_Town_Nm

, scga.dmuc_bhnp_cell_site_cd AS Dmuc_Bh_Non_Psd_Cell_Cd
, scga.dmuc_bh_non_psd_regn AS Dmuc_Bh_Non_Psd_Netwk_Regn_Nm
, scga.dmuc_bh_non_psd_prov_nm AS Dmuc_Bh_Non_Psd_Prov_Nm
, scga.dmuc_bh_non_psd_suburb AS Dmuc_Bh_Non_Psd_Suburb_Nm
, scga.dmuc_bh_non_psd_town AS Dmuc_Bh_Non_Psd_Town_Nm

, COALESCE(NULLIFZERO(dmuc_abh.ah_cntr), NULLIFZERO(msng_dmuc_ab24.ah_cntr)) AS Dmuc_Ah_Distnct_Cell_Site_Qty
, COALESCE(NULLIFZERO(dmuc_abh.bh_cntr), NULLIFZERO(msng_dmuc_ab24.bh_cntr)) AS Dmuc_Bh_Distnct_Cell_Site_Qty
, COALESCE(NULLIFZERO(dmuc_abh.ab24h_cntr), NULLIFZERO(msng_dmuc_ab24.ab24h_cntr)) AS Dmuc_24H_Distnct_Cell_Site_Qty

FROM dev_ads_tmp.subs_conn_primy scpm
LEFT JOIN dev_al.billg_dtl bad ON scpm.subs_id = bad.subs_id
LEFT JOIN dev_al.acct_hldr ahid ON scpm.subs_id = ahid.subs_id --driving table. 1st taken from bad.
LEFT JOIN dev_al.geo_area scga ON scpm.subs_id = scga.subs_id --driving table. 1st taken from bad.
LEFT JOIN dev_al.demog sadv ON scpm.subs_id = sadv.subs_id --driving table. 1st taken from bad.
LEFT JOIN ( SELECT subs_id
, party_subs_end_dttm
FROM dev_mde.party_subs
WHERE party_subs_role_cd = 'sr'
AND party_subs_end_dttm = '2050-12-31 23:59:59'
AND prcss_clsd_dttm = '2050-12-31 23:59:59'
QUALIFY MAX(party_subs_start_dttm)OVER (PARTITION BY subs_id) = party_subs_start_dttm
) p_s ON p_s.subs_id = scpm.subs_id
LEFT JOIN (

SEL
Subs_Id,
SUM (case when Subs_Metric_Type_Cd = 'AH_DISTINCT_CELL_SITE' then Subs_Metric_Cnt else null end ) as ah_cntr,
sum (case when Subs_Metric_Type_Cd = 'BH_DISTINCT_CELL_SITE' then Subs_Metric_Cnt else null end ) as bh_cntr,
sum (case when Subs_Metric_Type_Cd = '24H_DISTINCT_CELL_SITE' then Subs_Metric_Cnt else null end ) as ab24h_cntr
from (
Select
Subs_Id,
Subs_Metric_Type_Cd,
Subs_Metric_Cnt,
Subs_Metric_Start_Dttm,
Subs_Metric_End_Dttm
from dev_mde.SUBS_METRIC
qualify row_number () over (partition by Subs_Id, Subs_Metric_Type_Cd order by Subs_Metric_End_Dttm desc ) =1
where Subs_Metric_Type_Cd in ( 'AH_DISTINCT_CELL_SITE' ,'BH_DISTINCT_CELL_SITE' , '24H_DISTINCT_CELL_SITE')
) subs_metric
group by 1
) dmuc_abh ON scpm.subs_id = dmuc_abh.subs_id
LEFT JOIN (
SELECT sc.subs_id
, COUNT(DISTINCT CASE WHEN sc.subs_cell_rsn_cd LIKE ('DMUC-AH%') THEN c.cell_site_cd END) ah_cntr
, COUNT(DISTINCT CASE WHEN sc.subs_cell_rsn_cd LIKE ('DMUC-BH%') THEN c.cell_site_cd END) bh_cntr
, COUNT(DISTINCT CASE WHEN sc.subs_cell_rsn_cd LIKE ANY ('DMUC-AH%','DMUC-BH%') THEN c.cell_site_cd END) ab24h_cntr
FROM dev_mde.subs_cell sc
INNER JOIN dev_mde.subs_stat sos ON sos.subs_id=sc.subs_id AND sc.subs_cell_end_dttm = CAST('2050-12-31 23:59:59' AS TIMESTAMP(0) FORMAT 'yyyy-mm-ddbhh:mi:ss')
INNER JOIN dev_mde.cell c ON c.cell_id = sc.cell_id
AND (sos.pmt_meth_cd IN ('m','p','c') AND sos.offg_stat_type_cd = 'a' AND sos.subs_offg_end_dttm = CAST('2050-12-31 23:59:59' AS TIMESTAMP(0) FORMAT 'yyyy-mm-ddbhh:mi:ss'))
AND c.prcss_clsd_dttm=CAST('2050-12-31 23:59:59' AS TIMESTAMP(0))
WHERE sc.subs_cell_rsn_cd IN ('DMUC-AHP','DMUC-AHNP','DMUC-BHP','DMUC-BHNP')
GROUP BY 1
) msng_dmuc_ab24 ON scpm.subs_id = msng_dmuc_ab24.subs_id
LEFT JOIN (

SELECT
subs_desc.subs_id AS subs_id
, MAX(CASE WHEN subs_desc.desc_cd = 'onyx_stat_start_dt' THEN CAST(subs_desc.desc_dttm AS DATE) END) AS Vip_Onyx_Start_Dt
, MAX(CASE WHEN subs_desc.desc_cd = 'onyx_stat_end_dt' THEN CAST(subs_desc.desc_dttm AS DATE) END) AS Vip_Onyx_End_Dt
, MAX(CASE WHEN subs_desc.desc_cd = 'pltnm_stat_start_dt' THEN CAST(subs_desc.desc_dttm AS DATE) END) AS Vip_Platinum_Start_Dt
, MAX(CASE WHEN subs_desc.desc_cd = 'pltnm_stat_end_dt' THEN CAST(subs_desc.desc_dttm AS DATE) END) AS Vip_Platinum_End_Dt
GROUP BY 1
HAVING (Vip_onyx_end_dt IS NOT NULL AND Vip_onyx_end_dt > (SELECT CURRENT_INTEGRATION_DATE FROM dev_wrk.CURRENT_INTEGRATION_DATE_V))
OR (Vip_platinum_end_dt IS NOT NULL AND Vip_platinum_end_dt > (SELECT CURRENT_INTEGRATION_DATE FROM dev_wrk.CURRENT_INTEGRATION_DATE_V))
FROM dev_mde.subs_desc subs_desc
) onyx_pltnm ON scpm.subs_id = onyx_pltnm.subs_id;
1 REPLY
Senior Apprentice

Re: Query running for long time

Hi,

Some more info would be useful. Can you provide:

- an EXPLAIN of this query?

- the DDL for the tables/views involved

- the step level info for running this query from query log (table dbc.dbqlsteptbl or view dbc.qrylogstepsv)

- what is your expectation/desire for run-time for this query?

 

In your post you talk about 'primary key' joins. Do you mean 'primary key' or do you mean 'primary index'? These may not be the same columns in your tables. if not then that may be why you're getting an FTS access.

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com