Archives of the TeradataForum

Message Posted: Mon, 30 Oct 2006 @ 15:12:55 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Sunposis and long running query

From:		Anomy Anom

<-- Anonymously Posted: Monday, October 30, 2006 10:03 -->

Hello Gurus,

We are using a ETL tool(ELT rather) called sunposis to load our target tables. One of the queries generated by sunopsis is taking too long to execute. Pasted below is the query as well as the Explain for it. Can you please suggest a few tips as to how to make this run faster. We are constrained by the fact that we dont have the liberty to create intermediate temporary tables.

     Select
             AGR.Account_Num,
             AGR.Account_Modifier_Num,
             KEY1.FEATURE_ID,
             STG.BCM_OPENED_DT,
             NULL,
             NULL,
             NULL,
             CASE WHEN TRIM(STG3.BC37_OPERATOR)  = '+'  THEN
     TRIM(STG2.BC04_RATE_1) + TRIM(STG3.BC37_BASE_RATE)
      WHEN TRIM(STG3.BC37_OPERATOR)  = '-'  THEN
     TRIM(STG2.BC04_RATE_1) - TRIM(STG3.BC37_BASE_RATE)
     END,
             STG.BCM_BONUS_POINTS,
             STG.BCM_CARD_FEE_IND,
             NULL,
             NULL,
             NULL,
             NULL,
             'B2K',
             1,
             1 ,

             'I' IND_UPDATE

     from         EDSDV1_STAGING.STG_SB2K_BCMASTER    STG
     LEFT OUTER JOIN EDSDV1_STAGING.STG_SB2K_BCFINSG    STG1
     ON STG.BCM_ACCOUNT_NO1=STG1.BCF_ACCOUNT_NO1,
      EDSDV1_STAGING.STG_SB2K_BC04_RT   STG2,
      EDSDV1_STAGING.STG_SB2K_BC37_INT_RT   STG3,
      EDSDV1_KEY.KEY_FEATURE   KEY1,
     EDSDV1_MODEL.AGREEMENT   AGR
     where      (1=1)
      And
     (TRIM('BC04'||'01'||TRIM(STG1.BCF_LAST_RATE_CODE)||TRIM(STG1.BCF_LAST_RA
     TE_CODE_SEQ )||'2') =
     TRIM(STG2.BC04_TABLE_IDENT)||TRIM(STG2.BC04_REC_KEY_SUB)||TRIM(STG2.BC04
     _REC_NO))
     AND (TRIM('BC37'||'01'||TRIM(STG2.BC04_VARIABLE)||'1') =
     TRIM(STG3.BC37_TABLE_IDENTY
     )||TRIM(STG3.BC37_SUB_KEY1)||TRIM(STG3.BC37_RATE_CODE)||TRIM(STG3.BC37_S
     UB_KEY2))
     AND (STG.BCM_ACCOUNT_NO1 = substr(AGR.Account_Num,1,16)
     and AGR.Source_System_Cd='B2K'
     AND STG.EDW_FILE_ID=AGR.Edw_File_Id)
     And (KEY1.FEATURE_CD = 'Points')
      And (STG1.EDW_FILE_ID=20060929)

Explain

Explanation --------------------------------------------------

1) First, we lock a distinct EDSDV1_STAGING."pseudo table" for read on a RowHash to prevent global deadlock for EDSDV1_STAGING.STG.

2) Next, we lock a distinct EDSDV1_STAGING."pseudo table" for read on a RowHash to prevent global deadlock for EDSDV1_STAGING.STG1.

3) We lock a distinct EDSDV1_STAGING."pseudo table" for read on a RowHash to prevent global deadlock for EDSDV1_STAGING.STG3.

4) We lock a distinct EDSDV1_STAGING."pseudo table" for read on a RowHash to prevent global deadlock for EDSDV1_STAGING.STG2.

5) We lock a distinct EDSDV1_MODEL."pseudo table" for read on a RowHash to prevent global deadlock for EDSDV1_MODEL.AGR.

6) We lock a distinct EDSDV1_KEY."pseudo table" for read on a RowHash to prevent global deadlock for EDSDV1_KEY.KEY1.

7) We lock EDSDV1_STAGING.STG for read, we lock EDSDV1_STAGING.STG1 for read, we lock EDSDV1_STAGING.STG3 for read, we lock EDSDV1_STAGING.STG2 for read, we lock EDSDV1_MODEL.AGR for read, and we lock EDSDV1_KEY.KEY1 for read.

8) We execute the following steps in parallel.

1) We do an all-AMPs RETRIEVE step from EDSDV1_MODEL.AGR by way of an all-rows scan with a condition of ("(EDSDV1_MODEL.AGR.Source_System_Cd = 'B2K ') AND (1 = 1)") into Spool 2 (all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 2 by row hash. The size of Spool 2 is estimated with no confidence to be 341,487 rows. The estimated time for this step is 4.45 seconds.

2) We do an all-AMPs RETRIEVE step from EDSDV1_STAGING.STG by way of an all-rows scan with no residual conditions into Spool 3 (all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 3 by row hash. The result spool file will not be cached in memory. The size of Spool 3 is estimated with low confidence to be 18,618,534 rows. The estimated time for this step is 1 minute.

3) We do an all-AMPs RETRIEVE step from EDSDV1_STAGING.STG3 by way of an all-rows scan with no residual conditions into Spool 4 (all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 4 by row hash. The size of Spool 4 is estimated with low confidence to be 18 rows. The estimated time for this step is 0.00 seconds.

4) We do an all-AMPs RETRIEVE step from EDSDV1_STAGING.STG2 by way of an all-rows scan with no residual conditions into Spool 5 (all_amps), which is redistributed by hash code 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 18 rows. The estimated time for this step is 0.00 seconds.

9) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of a RowHash match scan, which is joined to Spool 5 (Last Use) by way of a RowHash match scan. Spool 4 and Spool 5 are joined using a merge join, with a join condition of ("(TRIM(BOTH FROM (('BC37'||'01')||(TRIM(BOTH FROM {RightTable}.BC04_VARIABLE)))||'1'))= ((((TRIM(BOTH FROM {LeftTable}.BC37_TABLE_IDENTY))||(TRIM(BOTH FROM {LeftTable}.BC37_SUB_KEY1 )))||(TRIM(BOTH FROM {LeftTable}.BC37_RATE_CODE )))||(TRIM(BOTH FROM {LeftTable}.BC37_SUB_KEY2 )))"). The result goes into Spool 6 (all_amps), which is duplicated on all AMPs. The size of Spool 6 is estimated with no confidence to be 1,386 rows. The estimated time for this step is 0.02 seconds.

10) We execute the following steps in parallel.

1) We do an all-AMPs JOIN step from EDSDV1_KEY.KEY1 by way of an all-rows scan with a condition of ("EDSDV1_KEY.KEY1.FEATURE_CD = 'Points'"), which is joined to Spool 6 (Last Use) by way of an all-rows scan. EDSDV1_KEY.KEY1 and Spool 6 are joined using a product join, with a join condition of ("(1=1)"). The result goes into Spool 7 (all_amps), which is duplicated on all AMPs. Then we do a SORT to order Spool 7 by row hash. The size of Spool 7 is estimated with no confidence to be 37,422 rows. The estimated time for this step is 0.04 seconds.

2) We do an all-AMPs RETRIEVE step from EDSDV1_STAGING.STG1 by way of an all-rows scan with a condition of ("(EDSDV1_STAGING.STG1.EDW_FILE_ID = 20060929.) AND (NOT (EDSDV1_STAGING.STG1.BCF_ACCOUNT_NO1 IS NULL ))") into Spool 8 (all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 8 by row hash. The size of Spool 8 is estimated with no confidence to be 1,066,785 rows. The estimated time for this step is 4.38 seconds.

11) We execute the following steps in parallel.

1) We do an all-AMPs JOIN step from Spool 7 (Last Use) by way of a RowHash match scan, which is joined to Spool 8 (Last Use) by way of a RowHash match scan. Spool 7 and Spool 8 are joined using a merge join, with a join condition of ("(TRIM(BOTH FROM ((('BC04'||'01')||(TRIM(BOTH FROM {RightTable}.BCF_LAST_RATE_CODE )))||(TRANSLATE((TRIM(BOTH FROM {RightTable}.BCF_LAST_RATE_CODE_SEQ (VARCHAR(6), CHARACTER SET LATIN, NOT CASESPECIFIC, FORMAT '-(5)9')))USING LATIN_TO_UNICODE)))||'2'))= (((TRIM(BOTH FROM {LeftTable}.BC04_TABLE_IDENT ))||(TRIM(BOTH FROM {LeftTable}.BC04_REC_KEY_SUB )))||(TRIM(BOTH FROM {LeftTable}.BC04_REC_NO )))"). The result goes into Spool 9 (all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 9 by row hash. The result spool file will not be cached in memory. The size of Spool 9 is estimated with no confidence to be 48,641,185 rows. The estimated time for this step is 2 minutes and 5 seconds.

2) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of a RowHash match scan, which is joined to Spool 3 (Last Use) by way of a RowHash match scan. Spool 2 and Spool 3 are joined using a merge join, with a join condition of ("(BCM_ACCOUNT_NO1 = (SUBSTR(Account_Num ,1 ,16 ))) AND (EDW_FILE_ID = Edw_File_Id)"). The result goes into Spool 10 (all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 10 by row hash. The result spool file will not be cached in memory. The size of Spool 10 is estimated with no confidence to be 1,473,489,063 rows. The estimated time for this step is 2 hours and 41 minutes.

12) We do an all-AMPs JOIN step from Spool 9 (Last Use) by way of a RowHash match scan, which is joined to Spool 10 (Last Use) by way of a RowHash match scan. Spool 9 and Spool 10 are joined using a merge join, with a join condition of ("BCM_ACCOUNT_NO1 = BCF_ACCOUNT_NO1"). The result goes into Spool 1 (group_amps), which is built locally on the AMPs. The result spool file will not be cached in memory. The size of Spool 1 is estimated with no confidence to be 1,867,143,104,178 rows. The estimated time for this step is 534 hours and 22 minutes.

13) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.

-> The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 537 hours and 4 minutes.

We were mulling about USIs on BCM_ACCOUNT_NO1 and BCF_ACCOUNT_NO1 but since they are tables which are often muliloaded, we wan to avoid it.

Many thanks


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference