QUERY IS RUNNING LONG TIME(40 MINS) TO EXTRACT 1 LACK RECORDS

Database
Enthusiast

QUERY IS RUNNING LONG TIME(40 MINS) TO EXTRACT 1 LACK RECORDS

Hi All,

THE BELOW QUERY IS RUNNING LONG TIME(40 MINS) TO EXTRACT 1 LACK RECORDS.
WHEEN WE RUN SELECT PART, ITS EXTRACTING QUICKLY IN SECONDS.
WHEN WE RUN THE TOTAL QUERY ITS TAKING 40 MINS TO COMPLETE

Volatile DDL:
CREATE VOLATILE TABLE V_TRDT_DB_SKEW_FACT
(
DT_ID INTEGER
,DT DATE
,DB_TBL_ID INTEGER
,CUR_PRMNT_SPC FLOAT(8)
,PEAK_PRMNT_SPC FLOAT(8)
,SKEW_FCTR DECIMAL(9,4)
,DW_CRTD_DTM TIMESTAMP(6)
,DW_UPDT_DTM TIMESTAMP(6)
,STATUS CHAR(3)
)
PRIMARY INDEX ( DT_ID)
ON COMMIT PRESERVE ROWS
;

INSERT INTO V_TRDT_DB_SKEW_FACT
(
DT_ID
,DT
,DB_TBL_ID
,CUR_PRMNT_SPC
,PEAK_PRMNT_SPC
,SKEW_FCTR
,DW_CRTD_DTM
,DW_UPDT_DTM
,STATUS
)
SELECT
CDD.DT_ID
,CAST(CAST(CDD.DOW-1 AS INTEGER) +CDD.PPC_CUR_WK_STRT_DT AS DATE) AS DT
,DTD.DB_TBL_ID
,TSF.CUR_PRMNT_SPC
,TSF.PEAK_PRMNT_SPC
,TSF.SKEW_FCTR
,CURRENT_DATE
,CURRENT_DATE
,CASE
WHEN TDSF.DT_ID IS NULL AND TDSF.DB_TBL_ID IS NULL THEN 'I'
ELSE 'U'
END AS STATUS
FROM ODW_S1_STAGE.TRDT_SKEW_FCTR TSF
INNER JOIN DB_TBL_DIM DTD
ON TSF.DB_NM = DTD.DB_NM
AND TSF.TBL_NM = DTD.TBL_NM
INNER JOIN CALDR_DT_DIM CDD
ON TSF.CUR_DT = DT
LEFT OUTER JOIN TRDT_DB_SKEW_FACT TDSF
ON CDD.DT_ID = TDSF.DT_ID
AND DTD.DB_TBL_ID = TDSF.DB_TBL_ID
QUALIFY ROW_NUMBER() OVER(PARTITION BY DTD.DB_TBL_ID,DTD.DB_NM ORDER BY DTD.DB_TBL_ID)=1
;
3 REPLIES
Junior Contributor

Re: QUERY IS RUNNING LONG TIME(40 MINS) TO EXTRACT 1 LACK RECORDS

This problem is usually caused by duplicate row checks due to a bad Primary Index on a SET table.

What's the actual data in CDD.DT_ID?

Enthusiast

Re: QUERY IS RUNNING LONG TIME(40 MINS) TO EXTRACT 1 LACK RECORDS

Thanks Dieter. you are correct bcoz the issue is  with bad primary index.

am using CDD.DT_ID,DTD.DB_TBL_ID two index fields and i have created index only on DT_ID while creating volatile table.

i have modified the volatile table creation statement (PRIMARY INDEX  ( DT_ID,DB_TBL_ID)) and now its working fine.

i have tested it in SQL assisant and need to run through Bteq script. will let you know once testing is done.

Thanks,

Praveen.

Enthusiast

Re: QUERY IS RUNNING LONG TIME(40 MINS) TO EXTRACT 1 LACK RECORDS

CDD.DT_ID,DTD.DB_TBL_ID fields are having sequence numbers  by using IDENTITY  column.

Thanks,

Praveen.