Reduce runtime

Database
Visitor

Reduce runtime

Extract SQL  gets aborted due to workload mgmt rule of respnse time . This qry runs a full table scan on the biggest table (TRSN_TBL ),table cannot be partitioned.The workload mgmt rules cannot be changed so the only way is to rewrite the query to reduce the response time. Experts, any suggestions ?

dynamic explainselect        * from          table(SASTransform(  NEW VARIANT_TYPE ("VIEWDB"."EXT_VIEW"."NR" ,                                     cast("VIEWDB"."EXT_VIEW"."TRSN_KEY" as float) as "TRSN_KEY" ,                                     cast("VIEWDB"."EXT_VIEW"."TRSN_DT" as float) as "TRSN_DT" ,                                     cast("VIEWDB"."EXT_VIEW"."TRSN_AMT" as float) as "TRSN_AMT" ,                                    "VIEWDB"."EXT_VIEW"."CHCK_NR" , "VIEWDB"."EXT_VIEW"."TRSN_ACT_NR" ,                                    "VIEWDB"."EXT_VIEW"."BNK_NR" , "VIEWDB"."EXT_VIEW"."BNK_NM" ,                                    "VIEWDB"."EXT_VIEW"."RL_DSC" , "VIEWDB"."EXT_VIEW"."SQ_NR" ,                                    "VIEWDB"."EXT_VIEW"."ACT_NR" , "VIEWDB"."EXT_VIEW"."ACT_NR" ) ) RETURNS( rc INTEGER)) as _t1;  The following is the dynamic plan for the request.  1) First, we lock MAAIN_TBLDB.TRSN_TBL for access, we     lock MAAIN_TBLDB.CSH_TBL for access, we lock     MAAIN_TBLDB.BRG_TBL for access, and we lock     MAAIN_TBLDB.BNK_TBL for access.  2) Next, we do a single-AMP RETRIEVE step from     LKUPDB.DT_PARM_TBL by way of the unique primary     index "LKUPDB.DT_PARM_TBL.JB_NR = 105,     LKUPDB.DT_PARM_TBL.PRM_NM = 'START_DATE'" with     no residual conditions locking row for access into Spool 3     (one-amp), which is built locally on that AMP.  The size of Spool     3 is estimated with high confidence to be 1 row (25 bytes).  The     estimated time for this step is 0.00 seconds.  3) We do a single-AMP FEEDBACK RETRIEVE step from Spool 3 (Last Use).     The size is estimated with high confidence to be 1 row.  The     estimated time for this step is 0.01 seconds.  The actual size of     Spool 3 (Last Use) is 1 row (28 bytes).  4) We do a single-AMP RETRIEVE step from     LKUPDB.DT_PARM_TBL by way of the unique primary     index "LKUPDB.DT_PARM_TBL.JB_NR = 105,     LKUPDB.DT_PARM_TBL.PRM_NM = 'END_DATE'" with no     residual conditions locking row for access into Spool 4 (one-amp),     which is built locally on that AMP.  The size of Spool 4 is     estimated with high confidence to be 1 row (25 bytes).  The     estimated time for this step is 0.00 seconds.  5) We do a single-AMP FEEDBACK RETRIEVE step from Spool 4 (Last Use).     The size is estimated with high confidence to be 1 row.  The     estimated time for this step is 0.01 seconds.  The actual size of     Spool 4 (Last Use) is 1 row (28 bytes).  6) We send an END PLAN FRAGMENT step for plan fragment 1.  7) We execute the following steps in parallel.       1) We do an all-AMPs RETRIEVE step from          MAAIN_TBLDB.TRSN_TBL by way of an all-rows          scan with a condition of ("(NOT          (MAAIN_TBLDB.TRSN_TBL.TRSN_ACT_NR IS NULL ))          AND ((NOT (MAAIN_TBLDB.TRSN_TBL.CHK_NR          IS NULL )) AND ((NOT          (MAAIN_TBLDB.TRSN_TBL.TRSN_ACT_NR LIKE          '%E%')) AND ((NOT          (MAAIN_TBLDB.TRSN_TBL.CHK_NR LIKE          '%E%')) AND (MAAIN_TBLDB.TRSN_TBL.SRC_ID =          '**bleep**'))))") into Spool 5 (all_amps) (compressed columns          allowed), which is redistributed by hash code to all AMPs to          all AMPs.  Then we do a SORT to order Spool 5 by row hash.          The size of Spool 5 is estimated with low confidence to be          1,611,933,361 rows (174,088,802,988 bytes).  The estimated          time for this step is 4 minutes.       2) We do an all-AMPs RETRIEVE step from 30 partitions of          MAAIN_TBLDB.CSH_TBL with a condition of (          "(MAAIN_TBLDB.CSH_TBL.DT >= :*) AND          ((MAAIN_TBLDB.CSH_TBL.DT <= :*) AND          (MAAIN_TBLDB.CSH_TBL.SRC_ID = '**bleep**'))")          into Spool 6 (all_amps) (compressed columns allowed), which          is redistributed by hash code to all AMPs to all AMPs.  Then          we do a SORT to order Spool 6 by row hash.  The size of Spool          6 is estimated with high confidence to be 2,038,712,317 rows          (156,980,848,409 bytes).  The estimated time for this step is          39.27 seconds.       3) We do an all-AMPs RETRIEVE step from          MAAIN_TBLDB.BRG_TBL by way of an          all-rows scan with a condition of (          "MAAIN_TBLDB.BRG_TBL.SRC_ID =          '**bleep**'") into Spool 7 (all_amps) (compressed columns          allowed) fanned out into 49 hash join partitions, which is          redistributed by hash code to all AMPs to all AMPs.  The size          of Spool 7 is estimated with high confidence to be          1,825,991,711 rows (147,905,328,591 bytes).  The estimated          time for this step is 22.66 seconds.  8) We execute the following steps in parallel.       1) We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of          a RowHash match scan, which is joined to Spool 6 (Last Use)          by way of a RowHash match scan.  Spool 5 and Spool 6 are          joined using a merge join, with a join condition of (          "(TRSN_KEY = TRSN_KEY) AND (SRC_ID =          SRC_ID)").  The result goes into Spool 8 (all_amps)          (compressed columns allowed) fanned out into 49 hash join          partitions, which is built locally on the AMPs.  The size of          Spool 8 is estimated with low confidence to be 1,611,933,361          rows (277,252,538,092 bytes).  The estimated time for this          step is 20.80 seconds.       2) We do an all-AMPs RETRIEVE step from          MAAIN_TBLDB.BNK_TBL by way of an all-rows scan with          a condition of ("MAAIN_TBLDB.BNK_TBL.SRC_ID =          '**bleep**'") split into Spool 9 (all_amps) with a condition of          ("BK_KY IN (:*)") to qualify rows matching skewed rows of          the skewed relation (compressed columns allowed) and Spool 10          (all_amps) with remaining rows (compressed columns allowed).          Spool 9 is built locally on the AMPs.  The size of Spool 9 is          estimated with high confidence to be 50 rows.  Spool 10 is          duplicated on all AMPs.  Then we do a SORT to order Spool 10          by row hash.  The size of Spool 10 is estimated with high          confidence to be 1,941,884,640 rows.  The estimated time for          this step is 42.20 seconds.  9) We do an all-AMPs JOIN step from Spool 7 (Last Use) by way of an     all-rows scan, which is joined to Spool 8 (Last Use) by way of an     all-rows scan.  Spool 7 and Spool 8 are joined using a hash join     of 49 partitions, with a join condition of ("(SRC_ID =     SRC_ID) AND ((TRSN_KEY = TRSN_KEY) AND     ((SRC_ID = SRC_ID) AND (TRSN_KEY = TRSN_KEY     )))").  The result is split into Spool 11 (all_amps) with a     condition of ("BK_KY IN (:*)") to qualify skewed rows and Spool     12 (all_amps) with remaining rows.  Spool 11 is built locally on     the AMPs.  The size of Spool 11 is estimated with low confidence     to be 842,507,429 rows (188,721,664,096 bytes).  Spool 12 is built     locally on the AMPs.  Then we do a SORT to order Spool 12 by row     hash.  The size of Spool 12 is estimated with low confidence to be     769,425,932 rows (172,351,408,768 bytes).  The estimated time for     this step is 43.58 seconds. 10) We do an all-AMPs RETRIEVE step from Spool 9 (Last Use) by way of     an all-rows scan into Spool 13 (all_amps) (compressed columns     allowed), which is duplicated on all AMPs.  The size of Spool 13     is estimated with high confidence to be 72,000 rows (8,064,000     bytes).  The estimated time for this step is 0.02 seconds. 11) We do an all-AMPs JOIN step from Spool 13 (Last Use) by way of an     all-rows scan, which is joined to Spool 11 (Last Use) by way of an     all-rows scan.  Spool 13 and Spool 11 are joined using a single     partition hash join, with a join condition of ("(BK_KY =     BK_KY) AND ((SRC_ID = SRC_ID) AND ((SRC_ID =     SRC_ID) AND (SRC_ID = SRC_ID )))").  The result goes     into Spool 14 (all_amps), which is built locally on the AMPs.  The     size of Spool 14 is estimated with low confidence to be     842,507,429 rows (196,304,230,957 bytes).  The estimated time for     this step is 4.52 seconds. 12) We do an all-AMPs JOIN step from Spool 10 (Last Use) by way of a     RowHash match scan, which is joined to Spool 12 (Last Use) by way     of a RowHash match scan.  Spool 10 and Spool 12 are joined using a     merge join, with a join condition of ("(BK_KY = BK_KY) AND     ((SRC_ID = SRC_ID) AND ((SRC_ID = SRC_ID) AND     (SRC_ID = SRC_ID )))").  The result goes into Spool 14     (all_amps), which is built locally on the AMPs.  The size of Spool     14 is estimated with low confidence to be 769,425,932 rows (     179,276,242,156 bytes).  The estimated time for this step is 18.92     seconds. 13) We do an all-AMPs RETRIEVE step from Spool 14 (Last Use) by way of     an all-rows scan into Spool 1 (all_amps) (compressed columns     allowed), which is built locally on the AMPs.  The size of Spool 1     is estimated with low confidence to be 1,611,933,361 rows (     388,475,940,001 bytes).  The estimated time for this step is 18.92     seconds. 14) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of     an all-rows scan executing table function DB.SASTransform     into Spool 2 (used to materialize view, derived table, table     function or table operator _t1) (all_amps) (compressed columns     allowed), which is built locally on the AMPs.  The size of Spool 2     is estimated with low confidence to be 1,611,933,361 rows (     40,298,334,025 bytes).  The estimated time for this step is 24.91     seconds. 15) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of     an all-rows scan into Spool 15 (all_amps), which is built locally     on the AMPs.  The size of Spool 15 is estimated with low     confidence to be 1,611,933,361 rows (40,298,334,025 bytes).  The     estimated time for this step is 2.78 seconds. 16) Finally, we send out an END TRANSACTION step to all AMPs involved     in processing the request. 

 

2 REPLIES
Teradata Employee

Re: Reduce runtime

According to the Explain, the view you are sourcing reads more than 14 billion rows over 10 different steps. The fact that this is estimated to run in 6.6 minutes is impressive, but it may be too much I/O for this workload setting. The only way to reduce the I/O is to copy the view (show view), look for things you can eliminate for this query, and create your own view - or just run the select from tables.  (Note that the retrieve from MAAIN_TBLDB.TRSN_TBL takes most of the estimated time, and MAAIN_TBLDB.CSH_TBL seems to take more I/O than anything else.)

Junior Contributor

Re: Reduce runtime

"rewrite the query":

- bunch of unreadable text

- using a view without showing the source code

- no additional information

 

My crystal ball seems to be broken.