All Forums Database
Ramkutty 3 posts Joined 08/12
06 Aug 2012
Need tips to fine tune quey which is running for long

SELECT
 ACCT_I
,EVNT_I
,MSUR_NODE_C
,PERD_TYPE_C
,PERD_D
,ACCT_LINK_D
,MSUR_A
FROM
tablename

 

The above select query is running for more than 40 minutes.

 

Can we have anyway to bring this time down.

 

Your responses are welcome !!!

ulrich 816 posts Joined 09/09
06 Aug 2012

You didn't poste any valuable info to support you here. So it ends up in guessing...

Is the table really a table? Or is it a view?

Run a show select * from tablename; 

Share the ddl(s)

How many rows does the table contain?

Is your query blocked? 

Try a lock row for access in front of your query

Is your query delayed - check in viewpoint, ask you DBAs...

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Ramkutty 3 posts Joined 08/12
06 Aug 2012

Hi,

 

The query which is trying to retrive is a view.

 

It needs 4 TB of spool space to retreive the rows.

 

So i cant able to give the no of rows presect currently.

 

 

 

 

ulrich 816 posts Joined 09/09
06 Aug 2012

But without sharing DDLs of the view, explain and stats info etc. nobody will be able to help you...

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Ramkutty 3 posts Joined 08/12
06 Aug 2012

DDL of the view

 Request Text
 REPLACE VIEW PVMSUR.GRD_INT_PAID_BMASAP
     (
       ACCT_I
      ,EVNT_I
      ,MSUR_NODE_C
      ,PERD_TYPE_C
      ,PERD_D
      ,ACCT_LINK_D
      ,MSUR_A
     )
     AS
     SELECT
       ACCT_I
      ,EVNT_I
      ,MSUR_NODE_C
      ,'MNTH'      AS PERD_TYPE_C
      ,MSUR_PERD_D AS PERD_D
      ,ACCT_LINK_D
      ,MSUR_A
     FROM (
       SELECT
         FINEVNT1.ACCT_I                                        AS ACCT_I
        ,FINEVNT1.EVNT_I                                        AS EVNT_I
        ,EVNT1.EVNT_ACTV_TYPE_C
        ,CASE
           WHEN ANCS_GL_ACCT_NODE_C = 'NETINTONASSETS_08'
             THEN 'INT_INC_PAID'
           ELSE 'INT_EXP_PAID'
         END                                                    AS MSUR_NODE_C
      ------------------------------------------------
      --  THE FOLLOWING COLUMN INCLUDES TRANSACTION DATES ALONG WITH BACK DATED
      --  TRANSACTIONS FOR THE GIVEN MONTH
      ------------------------------------------------
        ,CASE
                    
     WHEN CALRPROS1.MNTHS1900 - CALREFFT1.MNTHS1900 > 1
                ------------------------------------------------
                 -- AND POST_DATE IS WITHIN 4TH WORKING DAYS OF THE NEXT MONTH
                ------------------------------------------------
                 THEN
               CASE
                 WHEN FINEVNT1.FNCL_ACCT_EVNT_PROS_D
              BETWEEN DT1.CURR_RPRT_PERD_STRT_D AND DT1.CURR_RPRT_PERD_FRTH_DAY_D
                ------------------------------------------------
                    -- SET POST_DATE AS MONTH START DATE OF
                    -- THE PREVIOUS MONTH TO THE REPORTING PERIOD
                ------------------------------------------------
                       THEN DT1.PREV_RPRT_PERD_STRT_D
                ------------------------------------------------
  
                        -- ELSE POST_DATE AS MONTH START DATE OF POST_DATE
                ------------------------------------------------
  
                   ELSE FINEVNT1.FNCL_ACCT_EVNT_PROS_D
                    - FINEVNT1.FNCL_ACCT_EVNT_PROS_D MOD 100 + 1
               END
            ------------------------------------------------
                  -- WHEN POST_DATE > VALUE_DATE BY  A MNTH
            ------------------------------------------------
           WHEN CALRPROS1.MNTHS1900 - CALREFFT1.MNTHS1900 = 1
            ------------------------------------------------
                  -- IF POST_DATE IS WITHIN THE 1ST WORKING DAY AND 3RD
                  -- WORKING DAY OF THE REPORT MNTH
            ------------------------------------------------
             THEN
               CASE
                 WHEN FINEVNT1.FNCL_ACCT_EVNT_PROS_D
              BETWEEN DT1.CURR_RPRT_PERD_STRT_D AND DT1.CURR_RPRT_PERD_FRTH_DAY_D
            ------------------------------------------------
                  -- THEN SET THE DATE AS THE MONTH START DATE OF VALUE_DATE
            ------------------------------------------------
                   THEN  FINEVNT1.FNCL_ACCT_EVNT_EFFT_D
                 ELSE  FINEVNT1.FNCL_ACCT_EVNT_PROS_D
               END
           ELSE FINEVNT1.FNCL_ACCT_EVNT_PROS_D
         END                                                    AS POST_DATE
        ,ADD_MONTHS(POST_DATE - POST_DATE MOD 100 + 1, 1)-1     AS MSUR_PERD_D
        ,FINEVNT1.FNCL_ACCT_EVNT_PROS_D AS ACCT_LINK_D
        ,GLEVNT1.PDCT_I
        ,GLEVNT1.GL_OFST_ACCT_I
        ,FINEVNT1.FNCL_ACCT_EVNT_A                              AS MSUR_A
    
       FROM PVTECH.FNCL_ACCT_EVNT FINEVNT1
    
       JOIN PVTECH.EVNT EVNT1
         ON FINEVNT1.EVNT_I = EVNT1.EVNT_I
    
       JOIN PVTECH.FNCL_GL_EVNT GLEVNT1
         ON FINEVNT1.EVNT_I =  GLEVNT1.EVNT_I
        
      JOIN PVTECH.GRD_GL_ACCT_MNGE_MEMB MEMB1
         ON GLEVNT1.GL_OFST_ACCT_I = MEMB1.GL_ACCT_I
    
       JOIN PVTECH.GRD_GL_ACCT_MNGE_ANCS ANCS1
         ON MEMB1.GL_ACCT_I = ANCS1.GL_ACCT_I
    
       JOIN ( -- START OF DT1
         SELECT
         PERD_CURR1.PREV_RPRT_PERD_STRT_D AS PREV_RPRT_PERD_STRT_D
            ,PERD_CURR1.FRTH_BUS_DAY_D        AS CURR_RPRT_PERD_FRTH_DAY_D
            ,PERD_FWD1.FWD_RPRT_PERD_END_D    AS CURR_RPRT_PERD_END_D        
            ,PERD_CURR1.CURR_RPRT_PERD_STRT_D AS CURR_RPRT_PERD_STRT_D
            ,PERD_FWD1.FWD_FRTH_BUS_DAY_D     AS FWD_RPRT_PERD_FRTH_D
       
     FROM (
               
     SELECT
            CALR_YEAR_N
           ,CALR_MNTH_N
           ,CALR_CALR_D AS FWD_FRTH_BUS_DAY_D
           ,CALR_CALR_D - EXTRACT(DAY FROM CALR_CALR_D) + 1 AS FWD_FRST_OF_MNTH_D
           ,FWD_FRST_OF_MNTH_D -1 AS FWD_RPRT_PERD_END_D
           ,ADD_MONTHS(FWD_FRST_OF_MNTH_D, -1) AS FWD_RPRT_PERD_STRT_D
         
     FROM PVTECH.GRD_RPRT_CALR_CLYR
               
     WHERE CALR_WEEK_DAY_N NOT IN (1,7)
                 
      AND CALR_NON_WORK_DAY_F = 'N'
      AND CALR_CALR_D BETWEEN CURRENT_DATE - 1096
      AND CURRENT_DATE + 31
         
               
     QUALIFY ROW_NUMBER() OVER (PARTITION BY CALR_YEAR_N, CALR_MNTH_N
                                          
     ORDER BY CALR_CALR_D) = 4
              ) PERD_FWD1
         
     LEFT JOIN (
               
     SELECT
             CALR_YEAR_N
            ,CALR_MNTH_N
            ,CALR_CALR_D AS FRTH_BUS_DAY_D
            ,CALR_CALR_D - EXTRACT(DAY FROM CALR_CALR_D) + 1 AS FRST_OF_MNTH_D
            ,CALR_MNTH_LAST_DAY_D AS CURR_RPRT_PERD_END_D
            ,CALR_MNTH_FRST_DAY_D AS CURR_RPRT_PERD_STRT_D
            ,ADD_MONTHS (CURR_RPRT_PERD_STRT_D, -1)  AS PREV_RPRT_PERD_STRT_D
               
     FROM PVTECH.GRD_RPRT_CALR_CLYR
               
     WHERE CALR_WEEK_DAY_N NOT IN (1,7)
                 
      AND CALR_NON_WORK_DAY_F = 'N'
                 
      AND CALR_CALR_D BETWEEN CURRENT_DATE - 1096
      AND CURRENT_DATE + 31
               
     QUALIFY ROW_NUMBER() OVER (PARTITION BY CALR_YEAR_N, CALR_MNTH_N
                                          
     ORDER BY CALR_CALR_D) = 4
              ) PERD_CURR1
               
    ON PERD_FWD1.FWD_RPRT_PERD_STRT_D = PERD_CURR1.CURR_RPRT_PERD_STRT_D
       ) AS DT1
         ON FINEVNT1.FNCL_ACCT_EVNT_PROS_D
           BETWEEN DT1.CURR_RPRT_PERD_STRT_D AND DT1.FWD_RPRT_PERD_FRTH_D
    
     -- TO EXCLUDE MASTER CONTRACTS  - USE NEW VIEW THAT CONTAINS EVENTS FOR
     -- BOTH MC ACCOUNTS AND PARTICIPATING ACCOUNTS
     
     LEFT OUTER JOIN
     ( SELECT EVNT_I
             ,FNCL_ACCT_EVNT_PROS_D
         FROM PVMSUR.SUBQ_MSTR_CNCT_PRTP_ACCT_EVNT PRTP
         JOIN PVTECH.ACCT_BASE BASE1
           ON PRTP.ACCT_I = BASE1.ACCT_I
       
         JOIN PVTECH.GRD_GNRC_MAP_CURR GGM
           ON BASE1.ACCT_CLAS_TYPE_C = GGM.SRCE_CHAR_1_C
         
          WHERE GGM.MAP_TYPE_C = 'SAP_MC_ACCT_CLAS_TYPE_C'
      
      ) REL1   
     
           ON FINEVNT1.EVNT_I =  REL1.EVNT_I
          AND REL1.FNCL_ACCT_EVNT_PROS_D
      BETWEEN DT1.CURR_RPRT_PERD_STRT_D AND DT1.CURR_RPRT_PERD_END_D
    
       INNER JOIN (
       -- TO ALLOW CALCULATION OF MONTHS DIFFERENCE BETWEEN FNCL_ACCT_EVNT_PROS_D
       -- AND FNCL_ACCT_EVNT_EFFT_D
         SELECT
           CALR_CALR_D
          ,CALR_MNTH_SNCE_1900_N AS MNTHS1900
         FROM PVTECH.GRD_RPRT_CALR_CLYR
         WHERE CALR_CALR_D BETWEEN CURRENT_DATE - 1096 AND CURRENT_DATE + 31
       ) CALRPROS1
         ON FINEVNT1.FNCL_ACCT_EVNT_PROS_D = CALRPROS1.CALR_CALR_D
    
       INNER JOIN (
       -- TO ALLOW CALCULATION OF MONTHS DIFFERENCE BETWEEN FNCL_ACCT_EVNT_PROS_D
       -- AND FNCL_ACCT_EVNT_EFFT_D
         SELECT
           CALR_CALR_D
          ,CALR_MNTH_SNCE_1900_N AS MNTHS1900
         FROM PVTECH.GRD_RPRT_CALR_CLYR
         WHERE CALR_CALR_D BETWEEN CURRENT_DATE - 1096 AND CURRENT_DATE + 31
       ) CALREFFT1
         ON FINEVNT1.FNCL_ACCT_EVNT_EFFT_D = CALREFFT1.CALR_CALR_D
    
       WHERE FINEVNT1.SRCE_SYST_C = 'SAP'
       AND ANCS1.ANCS_GL_ACCT_NODE_C IN('NETINTONASSETS_08', 'NETINTONLIABS_08')
     
         -- TO EXCLUDE MASTER CONTRACTS AND PARTICIPATION ACCOUNTS
         AND REL1.EVNT_I IS NULL
    
     ) DT2
     QUALIFY ROW_NUMBER()
             OVER (PARTITION BY ACCT_I, EVNT_I ORDER BY POST_DATE ASC) = 1
 ;
 

ulrich 816 posts Joined 09/09
06 Aug 2012

So funny - instead of a plain select x,y,z from table you have a complicated view with 231 lines of code accessing >10 tables/views using derived tables and qualify filters etc.

did you try to run 

show select * from PVMSUR.GRD_INT_PAID_BMASAP;

?

How many database objects are returned?

Beside 

1. Check the stats for all the tables involed

2. Review the join conditions in respect to the PI / PPI definitions.

not much can be said.

If this is not helping break the SQL into smaller peaces and use work tables as interim steps.

Ask and pay a consultent to review the process / design ;-).

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.