All Forums Database
krishna1985 13 posts Joined 08/16
31 Aug 2016
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 );

Regards, Hari Krishna
dnoeth 4628 posts Joined 11/04
01 Sep 2016

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

Dieter

krishna1985 13 posts Joined 08/16
02 Sep 2016

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

Regards,
Hari Krishna

You must sign in to leave a comment.