Insert taking long

Database
Enthusiast

Insert taking long

Hi Gurus,

I have the below script everything works well but at the bottom of the script we have insert in to statement ie for UDRBSCMS.RCD_TPB_OVERALL_DECISION and its taking ages to load. I am not sure whats wrong here. We jsut have 60000 records.

DROP TABLE RCD_TPB_OVERALL_DECISION;

CREATE TABLE .RCD_TPB_OVERALL_DECISION AS

(

SELECT *

FROM GLSEastHLOrigination_test     

WHERE APPT_ORIG_C IN ('3PCL','3PDM','3POR')

AND METRIC1 = 'AppToRDFN'

AND UNTILDATE BETWEEN ---'2016-08-20' and '2016-08-26'

DATE-60 AND DATE

)

WITH DATA PRIMARY INDEX (untildate)

;

--Alter theRCD_TPB_OVERALL_DECISION for additional fields to calculate the rolling 7 days median.

ALTER TABLE RCD_TPB_OVERALL_DECISION

ADD dt DATE,

ADD res INTEGER,

ADD from_dt DATE,

ADD to_dt DATE,

ADD DATA_FLAG_TYPE VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC

;     

--Collect Statistics on the table to ensure columns are indexed appropriately which helps to optimise the query.

COLLECT STATISTICS

COLUMN (APPT_ORIG_C)

,COLUMN(Homeseeker_F)

,COLUMN(NPW_F)

,COLUMN(Exception_Impact_CreditDecision)

,COLUMN(AMENDMENT_F)

,COLUMN(TTFD_Team)

ON RCD_TPB_OVERALL_DECISION

;

--Create volatile table which calculates the from and to date ranges to calculate the 7 days rolling median

---DROP TABLE RCD_DECISION_7_DAYS ;

CREATE TABLE.RCD_DECISION_7_DAYS AS

(

SEL

A.APPT_ORIG_C

,A.Homeseeker_F

,A.NPW_F

,A.Exception_Impact_CreditDecision

,A.AMENDMENT_F

,A.TTFD_Team

,MAX( CAST( UNTILDATE AS DATE)) OVER (PARTITION BY base_day. from_dt, base_day.to_dt ORDER BY  base_day. from_dt) AS dt                                            

,(a.CUST_DIFF) AS res                                                

, base_day.FROM_dt

, base_day.to_dt

,'TIME TO DECISION' AS DATA_FLAG_TYPE

FROM  RCD_TPB_OVERALL_DECISION A,

(

                                        SEL

                                        CAST(UNTILDATE AS DATE) AS DATE1

                                        ,B.DATE2-6 AS FROM_DT

                                        ,B.DATE2 AS TO_DT

                                        FROM RCD_TPB_OVERALL_DECISION A

                                        INNER JOIN

                                        (   SELECT DISTINCT

                                            CAST(UNTILDATE AS DATE) AS DATE2

                                            FROM  UDRBSCMS.RCD_TPB_OVERALL_DECISION)  B

                                        ON CAST(A.UNTILDATE AS DATE)=B.DATE2

                                        WHERE

                                        DATE1

                                     between  date-60 and date --'2016-08-14'

                                        AND

                                        DATE1=DATE2 AND

                                        DATE1 BETWEEN  DATE2-6 AND DATE2

                                        GROUP BY 1,2,3

                            ) AS base_day

                            WHERE

                            --a.CHANNEL = 'BROKER'

                            --AND

                            CAST(a.UNTILDATE AS DATE) BETWEEN base_day.FROM_DT AND base_day.TO_DT

                            )

                            WITH DATA ;

;

--Insert into RCD_TPB_OVERALL_DECISION table data for Time to Overall Decision rolling 7 days median.    

INSERT INTO RCD_TPB_OVERALL_DECISION(

APPT_ORIG_C

,Homeseeker_F

,NPW_F

,Exception_Impact_CreditDecision

,AMENDMENT_F

,TTFD_Team,

dt,

res,

FROM_dt,

to_dt,

DATA_FLAG_TYPE

  )

SELECT

APPT_ORIG_C

,Homeseeker_F

,NPW_F

,Exception_Impact_CreditDecision

,AMENDMENT_F

,TTFD_Team,

dt,

res,

FROM_dt,

to_dt,

DATA_FLAG_TYPE

FROM RCD_DECISION_7_DAYS

;

-------------------------DDL of RCD_TPB_OVERALL_DECISION--------

show table  RCD_TPB_OVERALL_DECISION

CREATE SET TABLE RCD_TPB_OVERALL_DECISION ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      APPT_I VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,

      APPT_N VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

      STUS_C VARCHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC,

      STUS_M VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,

      start_appt TIMESTAMP(6),

      end_appt DATE FORMAT 'yyyy-mm-dd',

      metric1 VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,

      FromDate TIMESTAMP(6),

      start_team TIMESTAMP(6),

      end_team DATE FORMAT 'yyyy-mm-dd',

      WIM_st_bd INTEGER,

      WIM_st_cd INTEGER,

      UntilDate DATE FORMAT 'yyyy-mm-dd',

      tWIM_end_bd_C INTEGER,

      tWIM_end_cd_C INTEGER,

      tWIM_end_bd_T INTEGER,

      tWIM_end_cd_T INTEGER,

      APPT_ORIG_C CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC,

      appt_crat_d DATE FORMAT 'yyyy-mm-dd',

      WIM_diff_C INTEGER,

      WIM_diff_T INTEGER,

      WIM_diff_C_dt DECIMAL(38,2),

      WIM_diff_C_dt_cal DECIMAL(38,2),

      WIM_diff_T_dt DECIMAL(38,2),

      WIM_diff_T_dt_cal DECIMAL(38,2),

      PURP_TYPE_C CHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      PURP_X VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,

      PurpGroup VARCHAR(12) CHARACTER SET UNICODE NOT CASESPECIFIC,

      "Origination State" VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,

      AppRegion VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,

      CTU_F VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC,

      OffThePLan_F VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC,

      HomeSeeker_F VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC,

      Simple_F CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,

      ProgressPayment_F VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC,

      tWIM_Newappt_bd INTEGER,

      tWIM_Newappt_cd INTEGER,

      Newappt TIMESTAMP(6),

      eff_flag BYTEINT,

      Metric VARCHAR(25) CHARACTER SET UNICODE NOT CASESPECIFIC,

      "Loan Amount" VARCHAR(400) CHARACTER SET UNICODE NOT CASESPECIFIC,

      Target BYTEINT,

      Exclusions VARCHAR(13) CHARACTER SET UNICODE NOT CASESPECIFIC,

      start_dt TIMESTAMP(6),

      end_bd INTEGER,

      start_bd INTEGER,

      Team_diff INTEGER,

      Cust_diff INTEGER,

      refresh_date DATE FORMAT 'yyyy-mm-dd',

      Valuation_F CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,

      DocIss_POS_F CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,

      DocIss_GLS_F CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,

      AutoDecision_F CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,

      AutoClose_F CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,

      NPW_F VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,

      Exception_Impact_CreditDecision INTEGER,

      Exception_Impact_CreditDecision_NumExceptions INTEGER,

      AMENDMENT_F VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,

      TTFD_Team VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

      dt DATE FORMAT 'yyyy-mm-dd',

      res INTEGER,

      from_dt DATE FORMAT 'yyyy-mm-dd',

      to_dt DATE FORMAT 'yyyy-mm-dd',

      DATA_FLAG_TYPE VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC)

PRIMARY INDEX ( APPT_I );

2 REPLIES
Senior Apprentice

Re: Insert taking long

SET Table plus bad Primary Index ('3PCL','3PDM','3POR')?

Enthusiast

Re: Insert taking long

Hi Dieter, thanks I will try to make all tables as multiset and will try to make another column as Primary Index. Thanks Sir..