Co-releated Update Statement Tuning

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Sam Durai
    #1

    Co-releated Update Statement Tuning

    A simple update involving two small tables takes 45 minutes to
    complete. I would appreciate if you can kindly help me to understand
    the root cause of this slowness. Users would be happy if I can bring
    the timing down to 5 minutes or less.

    Environment:
    DB2 V8.1 FP 12 / AIX 5.3
    Both are non-partitioned tables..residin g on a logically partitioned
    database

    Table1: STG.TB_FIN_BRAN CH has 34,658 records
    Table2: STG.TB_STATE has 108 records

    Update SQL :

    update stg.tb_fin_bran ch aa set (aa.branch_stat e) = ( select coalesce
    state,' ') from stg.tb_fin_bran ch br left outer join stg.tb_state st
    on r.branch_state = st.state where aa.branch_sk = br.branch_sk)

    Both tables has matching indexes and are in good shape as per runstat/
    reorgchk statistics

    Quote:
    Table DDL:
    $ db2 describe table STG.TB_FIN_BRAN CH

    Column Type Type
    name schema name Length Scale Nulls
    ------------------------------ --------- ------------------ --------
    ----- ------
    BRANCH_SK SYSIBM DECIMAL 10 0 No
    BRANCH SYSIBM VARCHAR 6 0 No
    BRANCH_ADDRESS SYSIBM VARCHAR 30 0 Yes
    BRANCH_CITY SYSIBM VARCHAR 25 0 Yes
    BRANCH_STATE SYSIBM VARCHAR 2 0 Yes
    BRANCH_ZIPCODE SYSIBM VARCHAR 9 0 Yes
    BRANCH_COUNTRY SYSIBM VARCHAR 2 0 Yes
    CLOSED_DATE SYSIBM TIMESTAMP 10 0 Yes
    CONTACT_SK SYSIBM DECIMAL 10 0 No
    CONTACT_AGENCY SYSIBM VARCHAR 35 0 Yes

    10 record(s) selected.

    $ db2 describe indexes for table STG.TB_FIN_BRAN CH show detail

    Index Index Unique Number of
    schema name rule columns Column names
    ------------------------------- ------------------ --------------
    --------------
    ------------------------------------------------------------
    STG WMX1 D 2 +BRANCH_STATE+B RANCH_SK
    STG WMX2 D 10 +CONTACT_SK+CON TACT_AGENCY+CLO SED_DATE+BRANCH _ZIPC ODE
    +BRANCH_CITY+BR ANCH_ADDRESS+BR ANCH+BRANCH_SK+ BR ANCH_COUNTRY
    +BRANCH_STATE

    Table 2 DDL:
    $ db2 describe table STG.TB_STATE

    Column Type Type
    name schema name Length Scale Nulls
    ------------------------------ --------- ------------------ --------
    ----- ------
    COUNTRY SYSIBM VARCHAR 2 0 No
    STATE SYSIBM VARCHAR 2 0 No
    STATE_NAME SYSIBM VARCHAR 100 0 No
    CTRY_STATE SYSIBM VARCHAR 100 0 No
    STATE_ORDER SYSIBM DECIMAL 10 0 No

    5 record(s) selected.

    $ db2 describe indexes for table STG.TB_STATE show detail

    Index Index Unique Number of
    schema name rule columns Column names
    ------------------------------- ------------------ --------------
    --------------
    ------------------------------------------------------------
    STG IX1 D 1 +STATE

    1 record(s) selected.

    Explain Plan:
    Access Plan:
    -----------
    Total Cost: 2.93797e+06
    Query Degree: 1

    Total Cost: 2.93797e+06
    Query Degree: 1

    Rows
    RETURN
    ( 1)
    Cost
    I/O
    |
    34658
    UPDATE
    ( 2)
    2.93797e+06
    69450.9
    /----+----\
    34658 34658
    FETCH TABLE: STG
    ( 3) TB_FIN_BRANCH
    2.07137e+06
    34792.9
    /----+----\
    34658 34658
    TBSCAN TABLE: STG
    ( 4) TB_FIN_BRANCH
    1.20477e+06
    134.857
    |
    34658
    TEMP
    ( 5)
    1.20473e+06
    134.857
    |
    34658
    NLJOIN
    ( 6)
    1.20472e+06
    134.857
    /---+---\
    34658 1
    IXSCAN FILTER
    ( 7) ( 8)
    446.489 456.5
    66.8571 66.8571
    | |
    34658 34981.9
    INDEX: STG HSJOIN
    WMX1 ( 9)
    450.066
    66.8571
    /-----+-----\
    34658 108
    IXSCAN IXSCAN
    ( 10) ( 11)
    446.489 0.0933342
    66.8571 0
    | |
    34658 108
    INDEX: STG INDEX: STG
    WMX1 IX1

    Tags: None
    • Serge Rielau
      #2
      Re: Co-releated Update Statement Tuning

      Sam Durai wrote:
      A simple update involving two small tables takes 45 minutes to
      complete. I would appreciate if you can kindly help me to understand
      the root cause of this slowness. Users would be happy if I can bring
      the timing down to 5 minutes or less.
      >
      Environment:
      DB2 V8.1 FP 12 / AIX 5.3
      Both are non-partitioned tables..residin g on a logically partitioned
      database
      >
      Table1: STG.TB_FIN_BRAN CH has 34,658 records
      Table2: STG.TB_STATE has 108 records
      >
      Update SQL :
      >
      update stg.tb_fin_bran ch aa set (aa.branch_stat e) = ( select coalesce
      state,' ') from stg.tb_fin_bran ch br left outer join stg.tb_state st
      on r.branch_state = st.state where aa.branch_sk = br.branch_sk)
      Let be translate this into English:
      You have a table TB_FIN_BRANCH with a column BRANCH_SK.
      Now you want to fill in an extra (new?) column BRANCH_STATE for _all_
      rows in the table based on TB_STATE.
      I think you are doing an extra join here that isn't needed:

      UPDATE stg.tb_fin_bran ch aa
      SET aa.branch_state = COALESCE((SELEC T state
      FROM stg.tb_fin_bran ch AS br
      WHERE aa.branch_sk = br.branch_sk),
      ' ');

      Cheers
      Serge
      --
      Serge Rielau
      DB2 Solutions Development
      IBM Toronto Lab

        Comment

        • jmuehe@chefscatalog.com
          #3
          Re: Co-releated Update Statement Tuning

          On Nov 6, 5:18 am, Serge Rielau <srie...@ca.ibm .comwrote:
          Sam Durai wrote:
          A simple update involving two small tables takes 45 minutes to
          complete. I would appreciate if you can kindly help me to understand
          the root cause of this slowness. Users would be happy if I can bring
          the timing down to 5 minutes or less.
          >
          Environment:
          DB2 V8.1 FP 12 / AIX 5.3
          Both are non-partitioned tables..residin g on a logically partitioned
          database
          >
          Table1: STG.TB_FIN_BRAN CH has 34,658 records
          Table2: STG.TB_STATE has 108 records
          >
          Update SQL :
          >
          update stg.tb_fin_bran ch aa set (aa.branch_stat e) = ( select coalesce
          state,' ') from stg.tb_fin_bran ch br left outer join stg.tb_state st
          on r.branch_state = st.state where aa.branch_sk = br.branch_sk)
          >
          Let be translate this into English:
          You have a table TB_FIN_BRANCH with a column BRANCH_SK.
          Now you want to fill in an extra (new?) column BRANCH_STATE for _all_
          rows in the table based on TB_STATE.
          I think you are doing an extra join here that isn't needed:
          >
          UPDATE stg.tb_fin_bran ch aa
              SET aa.branch_state = COALESCE((SELEC T state
                                              FROM stg.tb_fin_bran ch AS br
                                              WHERE aa.branch_sk = br.branch_sk),
                                             ' ');
          >
          Cheers
          Serge
          --
          Serge Rielau
          DB2 Solutions Development
          IBM Toronto Lab- Hide quoted text -
          >
          - Show quoted text -
          try this -- it should run much faster.

          Update Stg.Tb_Fin_Bran ch Aa
          Set Aa.Branch_State =
          (Select Coalesce(State, ' ')
          From Stg.Tb_State St Where Aa.Branch_Sk = Br.Branch_Sk)
          Where Aa.Branch_State !=
          (Select Coalesce(State, ' ')
          From Stg.Tb_State St Where Aa.Branch_Sk = Br.Branch_Sk)

            Comment

            • jmuehe@chefscatalog.com
              #4
              Re: Co-releated Update Statement Tuning

              On Nov 6, 1:18 pm, jmu...@chefscat alog.com wrote:
              On Nov 6, 5:18 am, Serge Rielau <srie...@ca.ibm .comwrote:
              >
              >
              >
              >
              >
              Sam Durai wrote:
              A simple update involving two small tables takes 45 minutes to
              complete. I would appreciate if you can kindly help me to understand
              the root cause of this slowness. Users would be happy if I can bring
              the timing down to 5 minutes or less.
              >
              Environment:
              DB2 V8.1 FP 12 / AIX 5.3
              Both are non-partitioned tables..residin g on a logically partitioned
              database
              >
              Table1: STG.TB_FIN_BRAN CH has 34,658 records
              Table2: STG.TB_STATE has 108 records
              >
              Update SQL :
              >
              update stg.tb_fin_bran ch aa set (aa.branch_stat e) = ( select coalesce
              state,' ') from stg.tb_fin_bran ch br left outer join stg.tb_state st
              on r.branch_state = st.state where aa.branch_sk = br.branch_sk)
              >
              Let be translate this into English:
              You have a table TB_FIN_BRANCH with a column BRANCH_SK.
              Now you want to fill in an extra (new?) column BRANCH_STATE for _all_
              rows in the table based on TB_STATE.
              I think you are doing an extra join here that isn't needed:
              >
              UPDATE stg.tb_fin_bran ch aa
                  SET aa.branch_state = COALESCE((SELEC T state
                                                 FROM stg.tb_fin_bran ch AS br
                                                 WHERE aa.branch_sk = br.branch_sk),
                                                 ' ');
              >
              Cheers
              Serge
              --
              Serge Rielau
              DB2 Solutions Development
              IBM Toronto Lab- Hide quoted text -
              >
              - Show quoted text -
              >
              try this -- it should run much faster.
              >
              Update Stg.Tb_Fin_Bran ch Aa
              Set Aa.Branch_State =
                      (Select Coalesce(State, ' ')
                      From Stg.Tb_State St Where Aa.Branch_Sk = Br.Branch_Sk)
              Where Aa.Branch_State !=
                      (Select Coalesce(State, ' ')
                      From Stg.Tb_State St Where Aa.Branch_Sk = Br.Branch_Sk)- Hide quoted text -
              >
              - Show quoted text -
              correction

              Update Stg.Tb_Fin_Bran ch Aa
              Set Aa.Branch_State =
              (Select Coalesce(State, ' ')
              From Stg.Tb_State St Where Aa.Branch_Sk = St.Branch_Sk)
              Where Aa.Branch_State !=
              (Select Coalesce(State, ' ')
              From Stg.Tb_State St Where Aa.Branch_Sk = St.Branch_Sk)

                Comment

                Previous template Next
                Working...