Need URGENT help on Update SQL tuning

Database

Need URGENT help on Update SQL tuning

Hello All,

I am new to this forum and Teradata as well. I am expected to tune a poor performing query but I have no idea how to proceed with understanding Explain plan and proceed with tuning.

Can anyone provide inputs on tuning?

UPDATE VISIT

FROM VISIT PREV, VISIT

SET

RECALL_STRING = CASE WHEN COALESCE(VISIT.CNT_OF_ALL_RLTD_RCAL,0) > 148 THEN CAST(VISIT.RECALL_STRING AS VARCHAR(2000)) || '*'

                                  ELSE CAST(VISIT.PREVIOUS_WR_NO AS VARCHAR(20)) || '-' || CAST(VISIT.PREVIOUS_WR_RFA_NO AS VARCHAR(10)) || '¿' || COALESCE(PREV.RECALL_STRING,'') END

,CNT_OF_ALL_RLTD_RCAL = COALESCE(PREV.CNT_OF_ALL_RLTD_RCAL,0) + 1

,CNT_ALL_VISITS_FOR_RLTD_RCAL = CASE WHEN PREV.PREVIOUS_WR_RFA_NO IS NULL THEN COALESCE(PREV.CNT_OF_GENUINE_TSK_ACTIVITES,0) ELSE COALESCE(PREV.CNT_ALL_VISITS_FOR_RLTD_RCAL,0) END + COALESCE(VISIT.CNT_OF_GENUINE_TSK_ACTIVITES,0)

,CNT_PRT_USD_PRVS_RLTD_RCAL = (COALESCE(PREV.CNT_PRT_USD_PRVS_RLTD_RCAL,0) + COALESCE(PREV.TOTAL_NO_OF_PARTS_USED,0))

,TOT_CST_PRT_USD_PRVS_RLTD_RCAL = (COALESCE(PREV.TOT_CST_PRT_USD_PRVS_RLTD_RCAL,0) + COALESCE(PREV.COST_OF_PARTS_USED,0))

,RECALLED_DURATION = CAST(VISIT.MAX_ACTY_CMPNENT_VST_DATE AS DATE) - CAST(PREV.MAX_ACTY_CMPNENT_VST_DATE AS DATE)

WHERE

VISIT.PREVIOUS_WR_NO IS NOT NULL

AND VISIT.PREVIOUS_WR_RFA_NO IS NOT NULL

AND VISIT.PREVIOUS_WR_NO = PREV.WR_NO

AND VISIT.PREVIOUS_WR_RFA_NO = PREV.WR_RFA_NO

AND COALESCE(length(OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(PREV.recall_string,'0'),'1'),'2'),'3'),'4'),'5'),'6'),'7'),'8'),'9'),'-')),0) = 39

Explain Plan:

This query is optimized using type 2 profile T2_Linux64, profileid 21.

  1) First, we lock a distinct EDWDEV04T_EDW."pseudo table" for write

     on a RowHash to prevent global deadlock for

     EDWDEV04T_EDW.VISIT.

  2) Next, we lock EDWDEV04T_EDW.VISIT in view

     edwdev04v_edw.VISIT for write.

  3) We do an all-AMPs RETRIEVE step from

     EDWDEV04T_EDW.VISIT in view

     edwdev04v_edw.VISIT by way of an all-rows scan with a

     condition of ("(NOT (EDWDEV04T_EDW.VISIT in view

     edwdev04v_edw.VISIT.PREVIOUS_WR_RFA_NO IS NULL )) AND

     (NOT (EDWDEV04T_EDW.VISIT in view

     edwdev04v_edw.VISIT .PREVIOUS_WR_NO IS NULL ))") 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 35,998,195 rows (

     935,953,070 bytes).  The estimated time for this step is 7.27

     seconds.

  4) We do an all-AMPs JOIN step from EDWDEV04T_EDW.VISIT

     in view edwdev04v_edw.VISIT by way of a RowHash match

     scan with a condition of ("(( CASE WHEN (NOT (SYSLIB.length (

     SYSLIB.OREPLACE (SYSLIB.OREPLACE (SYSLIB.OREPLACE (

     SYSLIB.OREPLACE (SYSLIB.OREPLACE (SYSLIB.OREPLACE (

     SYSLIB.OREPLACE (SYSLIB.OREPLACE (SYSLIB.OREPLACE (

     SYSLIB.OREPLACE (SYSLIB.OREPLACE (

     EDWDEV04T_EDW.VISIT in view

     edwdev04v_edw.VISIT.RECALL_STRING (VARCHAR(16000),

     CHARACTER SET LATIN, NOT CASESPECIFIC), '0'(VARCHAR(512),

     CHARACTER SET LATIN, NOT CASESPECIFIC)), '1'(VARCHAR(512),

     CHARACTER SET LATIN, NOT CASESPECIFIC)), '2'(VARCHAR(512),

     CHARACTER SET LATIN, NOT CASESPECIFIC)), '3'(VARCHAR(512),

     CHARACTER SET LATIN, NOT CASESPECIFIC)), '4'(VARCHAR(512),

     CHARACTER SET LATIN, NOT CASESPECIFIC)), '5'(VARCHAR(512),

     CHARACTER SET LATIN, NOT CASESPECIFIC)), '6'(VARCHAR(512),

     CHARACTER SET LATIN, NOT CASESPECIFIC)), '7'(VARCHAR(512),

     CHARACTER SET LATIN, NOT CASESPECIFIC)), '8'(VARCHAR(512),

     CHARACTER SET LATIN, NOT CASESPECIFIC)), '9'(VARCHAR(512),

     CHARACTER SET LATIN, NOT CASESPECIFIC)), '-'(VARCHAR(512),

     CHARACTER SET LATIN, NOT CASESPECIFIC))(VARCHAR(64000), CHARACTER

     SET LATIN, NOT CASESPECIFIC))IS NULL )) THEN (SYSLIB.length (

     SYSLIB.OREPLACE (SYSLIB.OREPLACE (SYSLIB.OREPLACE (

     SYSLIB.OREPLACE (SYSLIB.OREPLACE (SYSLIB.OREPLACE (

     SYSLIB.OREPLACE (SYSLIB.OREPLACE (SYSLIB.OREPLACE (

     SYSLIB.OREPLACE (SYSLIB.OREPLACE (

     EDWDEV04T_EDW.VISIT in view

     edwdev04v_edw.VISIT.RECALL_STRING (VARCHAR(16000),

     CHARACTER SET LATIN, NOT CASESPECIFIC), '0'(VARCHAR(512),

     CHARACTER SET LATIN, NOT CASESPECIFIC)), '1'(VARCHAR(512),

     CHARACTER SET LATIN, NOT CASESPECIFIC)), '2'(VARCHAR(512),

     CHARACTER SET LATIN, NOT CASESPECIFIC)), '3'(VARCHAR(512),

     CHARACTER SET LATIN, NOT CASESPECIFIC)), '4'(VARCHAR(512),

     CHARACTER SET LATIN, NOT CASESPECIFIC)), '5'(VARCHAR(512),

     CHARACTER SET LATIN, NOT CASESPECIFIC)), '6'(VARCHAR(512),

     CHARACTER SET LATIN, NOT CASESPECIFIC)), '7'(VARCHAR(512),

     CHARACTER SET LATIN, NOT CASESPECIFIC)), '8'(VARCHAR(512),

     CHARACTER SET LATIN, NOT CASESPECIFIC)), '9'(VARCHAR(512),

     CHARACTER SET LATIN, NOT CASESPECIFIC)), '-'(VARCHAR(512),

     CHARACTER SET LATIN, NOT CASESPECIFIC))(VARCHAR(64000), CHARACTER

     SET LATIN, NOT CASESPECIFIC))) ELSE (0) END ))= 39"), which is

     joined to Spool 2 (Last Use) by way of a RowHash match scan

     locking EDWDEV04T_EDW.VISIT for access.

     EDWDEV04T_EDW.VISIT  and Spool 2 are joined using a

     merge join, with a join condition of ("(PREVIOUS_WR_NO =

     EDWDEV04T_EDW.VISIT.WR_NO) AND (PREVIOUS_WR_RFA_NO =

     EDWDEV04T_EDW.VISIT.WR_RFA_NO)").  The result goes

     into Spool 1 (all_amps), which is redistributed by hash code to

     all AMPs.  Then we do a SORT to order Spool 1 by the sort key in

     spool field1.  The result spool file will not be cached in memory.

     The size of Spool 1 is estimated with no confidence to be

     159,983,970,179,290 rows (*** bytes).  The estimated time for this

     step is 217,983 hours and 39 minutes.

  5) We do a MERGE Update to EDWDEV04T_EDW.VISIT in view

     edwdev04v_edw.VISIT from Spool 1 (Last Use) via ROWID.

     The size is estimated with no confidence to be 159,983,970,179,290

     rows (*** bytes).  The estimated time for this step is 1,828,123

     hours and 47 minutes.

  6) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> No rows are returned to the user as the result of statement 1.

     The total estimated time is 2,046,107 hours and 26 minutes.

2 REPLIES
N/A

Re: Need URGENT help on Update SQL tuning

There's too much code:

- You don't need COALESCE for comparison, as NULL always compares to unknown

- You don't need IS NULL in WHERE, this is automatically added by the optimizer

- Replace OREPLACE with OTRANSLATE (as CarlosAL already adviced in another thread)

- Replace LENGTH with the builtin CHAR_LENGTH

Seems some stats are missing, too, could you post the output of a HELP STATS visit;

And to see the actual resource usage you need access to the QueryLog (DBQL), in best case it's enbled on steplevel.

Dieter

Re: Need URGENT help on Update SQL tuning

I would recommend PRISE Tuning Assistant to investigate performance problems, you can get a full functional free trial here: https://www.prisetools.com/productrequest