Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Posts
    58

    Unanswered: How to improve "MERGE INTO" query performance ?

    Hello all,

    For this, I have 1 large history table (30mio records).
    I have to merge a daily delta table (4mio records) into it.

    To do this, I wrote the stmt below (truncated because the msg is too long for this window). I tested the syntax with just a few lines and it works. Now, the query is running in production but it seems to take a very long time. in the task manager on the server, I can see almost no CPU-usage or extra memory consumption.

    What can be wrong ?
    Which parameters to check or increase ?
    Also, db2advis doesn't give anything back because it seems to be captured as a non valid sql stmt ....

    Many thanks in advance

    Peter



    CONNECT TO NEWSTEP user db2admin using admindb2;
    MERGE INTO DIFOT.EMEA_FEED_HIST as dest
    USING (SELECT KEY_SLS_ORDR_FACT_ID, KEY_DLVRY_FACT_ID, SOLD_TO_CUST, REGION, SUBRGN, SUBRGN_DESC,
    ACCT_CUST_GRP_CODE, ACCT_SPLY_CHN_GRP_CODE, SLS_ORG_CODE,
    SLS_ORG_DESC, PLNT_LOCTN_CODE, DIVISION,
    SLS_DOC_HDR_NUM_CODE, SLS_DOC_LN_ITM_NUM_CODE,
    SLS_DOC_SCHDL_LN_NUM_CODE, DLVRY_DOC_HDR_NUM_CODE,
    DLVRY_DOC_SCHDL_LN_NUM_CODE, KEY_CNCL_DATE_DIM_ID,
    SHP_TO_CUST, SHP_TO_COUNTRY, MATRL, KEY_CCD_DATE_DIM_ID,
    BUCKET_MONTH_DATE, KEY_CRD_DATE_DIM_ID, KEY_MAD_DIM_ID,
    DATE_OF_DLVR_DUE, CANCEL_DATE, AGID_DATE, PGID_DATE,
    SO_IDP_DATE, SCHDLD_BKNG_IN_DATE, PLND_SCHDLD_DT,
    OUTGOING_DT, AFS_CAT_CODE, CRDT_STAT_CODE, SO_ITM_CAT_CODE,
    SO_IDP_FLAG, DLVRY_CNT, ORDR_ENTRY_QTY, UNCNFRMD_QTY,
    CNFRMD_QTY, BAS_DLVRY_QTY, RSRVD_UNIT_QTY, OPEN_QTY,
    UNASSIGNED_QTY, ALLOC_F_QTY, ALLOC_T_QTY, DLVRY_UNIT_QTY,
    RJCTD_UNIT_QTY, UOM_CNVRSN_FCTR, SOS_BAS_UOM, CAT_CD,
    CHNL_CLASS, LIFECYCLE, SUB_CAT_CODE, TYPE_GRP, ACCT_TYPE,
    ACCT_TYPE_DESC, DIRECT_SHIP_FLAG, QUALITY_CD, ALLOC_D_QTY,
    SCHED_SC_QTY, SCHED_PH_QTY, CONTRACT_QTY, CONTRACT_FLAG,
    CREDIT_FLAG, DIST_METHOD, ALLOC_FB_QTY, ALLOC_FNB_QTY,
    EMEA_RPT_GRP, HOLD_TYPE, DLVRY_HOLD_QTY, ORDR_TYPE_CODE,
    CREDIT_HOLD_QTY, SHIP_ALLOC_QTY, SHIP_IN_PROCESS_QTY,
    FIRST_PRODUCT_OFFER_DATE, FLAG_AT_ONCE, FLAG_BGRADE,
    FLAG_CLOSEOUTS, FLAG_FUTURES, FLAG_PARTNERS, FLAG_STRATEGIC,
    MARKETING_CODE, LAUNCH_INDICATOR, CRDNTD_RLSE_CODE,
    CUST_ORDR_NUM_CODE, CMPLTNSS_FLAG, SLS_OFFICE,
    CNTRCT_START_DATE, CNTRCT_END_DATE, CNTRCT_DOC_HDR_NUM_CODE,
    OB_DLVRY_BLCK_CODE, RSRVD_IN_TRANSIT_QTY, RSRVD_PO_QTY,
    RSRVD_ON_HAND_QTY, DELIVERY_PRIORITY, DLVRY_GRP_CODE,
    MATRL_SIZE_CODE, SO_DLVRY_BLCK_CODE, SO_LN_DLVRY_BLCK_CODE,
    RTE_CODE, SHPPNG_COND, LDNG_PNT_LOCTN, ZZZ_RCRD_CREATE_DTTM,
    IDP_DATE, IDP_FLAG
    FROM DIFOT.EMEA_LOAD) as source
    ON ( dest.KEY_SLS_ORDR_FACT_ID = source. KEY_SLS_ORDR_FACT_ID AND
    dest.KEY_DLVRY_FACT_ID = source. KEY_DLVRY_FACT_ID)
    WHEN MATCHED THEN
    UPDATE SET
    SOLD_TO_CUST = source.SOLD_TO_CUST ,
    REGION = source.REGION,
    SUBRGN = source.SUBRGN,
    SUBRGN_DESC = source.SUBRGN_DESC ,
    ACCT_CUST_GRP_CODE = source.ACCT_CUST_GRP_CODE ,
    ACCT_SPLY_CHN_GRP_CODE = source.ACCT_SPLY_CHN_GRP_CODE ,
    SLS_ORG_CODE = source.SLS_ORG_CODE ,
    SLS_ORG_DESC = source.SLS_ORG_DESC ,
    PLNT_LOCTN_CODE = source.PLNT_LOCTN_CODE ,
    DIVISION = source.DIVISION ,
    SLS_DOC_HDR_NUM_CODE = source.SLS_DOC_HDR_NUM_CODE ,
    SLS_DOC_LN_ITM_NUM_CODE = source.SLS_DOC_LN_ITM_NUM_CODE ,
    SLS_DOC_SCHDL_LN_NUM_CODE = source.SLS_DOC_SCHDL_LN_NUM_CODE ,
    DLVRY_DOC_HDR_NUM_CODE = source.DLVRY_DOC_HDR_NUM_CODE ,
    DLVRY_DOC_SCHDL_LN_NUM_CODE = source.DLVRY_DOC_SCHDL_LN_NUM_CODE ,
    KEY_CNCL_DATE_DIM_ID = source.KEY_CNCL_DATE_DIM_ID ,
    SHP_TO_CUST = source.SHP_TO_CUST ,
    SHP_TO_COUNTRY = source.SHP_TO_COUNTRY ,
    MATRL = source.MATRL ,
    KEY_CCD_DATE_DIM_ID = source.KEY_CCD_DATE_DIM_ID ,
    BUCKET_MONTH_DATE = source.BUCKET_MONTH_DATE ,
    KEY_CRD_DATE_DIM_ID = source.KEY_CRD_DATE_DIM_ID ,
    KEY_MAD_DIM_ID = source.KEY_MAD_DIM_ID ,
    DATE_OF_DLVR_DUE = source.DATE_OF_DLVR_DUE ,
    CANCEL_DATE = source.CANCEL_DATE ,
    AGID_DATE = source.AGID_DATE ,
    PGID_DATE = source.PGID_DATE ,
    SO_IDP_DATE = source.SO_IDP_DATE ,
    SCHDLD_BKNG_IN_DATE = source.SCHDLD_BKNG_IN_DATE ,
    PLND_SCHDLD_DT = source.PLND_SCHDLD_DT ,
    OUTGOING_DT = source.OUTGOING_DT ,
    AFS_CAT_CODE = source.AFS_CAT_CODE ,
    CRDT_STAT_CODE = source.CRDT_STAT_CODE ,
    SO_ITM_CAT_CODE = source.SO_ITM_CAT_CODE ,
    SO_IDP_FLAG = source.SO_IDP_FLAG ,
    DLVRY_CNT = source.DLVRY_CNT ,
    ORDR_ENTRY_QTY = source.ORDR_ENTRY_QTY ,
    UNCNFRMD_QTY = source.UNCNFRMD_QTY ,
    CNFRMD_QTY = source.CNFRMD_QTY ,
    BAS_DLVRY_QTY = source.BAS_DLVRY_QTY ,
    RSRVD_UNIT_QTY = source.RSRVD_UNIT_QTY ,
    OPEN_QTY = source.OPEN_QTY ,
    UNASSIGNED_QTY = source.UNASSIGNED_QTY ,
    ALLOC_F_QTY = source.ALLOC_F_QTY ,
    ALLOC_T_QTY = source.ALLOC_T_QTY ,
    DLVRY_UNIT_QTY = source.DLVRY_UNIT_QTY ,
    RJCTD_UNIT_QTY = source.RJCTD_UNIT_QTY ,
    UOM_CNVRSN_FCTR = source.UOM_CNVRSN_FCTR ,
    SOS_BAS_UOM = source.SOS_BAS_UOM ,
    CAT_CD = source.CAT_CD ,
    CHNL_CLASS = source.CHNL_CLASS ,
    LIFECYCLE = source.LIFECYCLE ,
    SUB_CAT_CODE = source.SUB_CAT_CODE ,
    TYPE_GRP = source.TYPE_GRP ,
    ACCT_TYPE = source.ACCT_TYPE ,
    ACCT_TYPE_DESC = source.ACCT_TYPE_DESC ,
    DIRECT_SHIP_FLAG = source.DIRECT_SHIP_FLAG,
    QUALITY_CD = source.QUALITY_CD ,
    ALLOC_D_QTY = source.ALLOC_D_QTY ,
    SCHED_SC_QTY = source.SCHED_SC_QTY ,
    SCHED_PH_QTY = source.SCHED_PH_QTY ,
    CONTRACT_QTY = source.CONTRACT_QTY ,
    CONTRACT_FLAG = source.CONTRACT_FLAG ,
    CREDIT_FLAG = source.CREDIT_FLAG ,
    DIST_METHOD = source.DIST_METHOD ,
    ALLOC_FB_QTY = source.ALLOC_FB_QTY ,
    ALLOC_FNB_QTY = source.ALLOC_FNB_QTY ,
    EMEA_RPT_GRP = source.EMEA_RPT_GRP ,
    HOLD_TYPE = source.HOLD_TYPE ,
    DLVRY_HOLD_QTY = source.DLVRY_HOLD_QTY ,
    ORDR_TYPE_CODE = source.ORDR_TYPE_CODE ,
    CREDIT_HOLD_QTY = source.CREDIT_HOLD_QTY ,
    SHIP_ALLOC_QTY = source.SHIP_ALLOC_QTY ,
    SHIP_IN_PROCESS_QTY = source.SHIP_IN_PROCESS_QTY,
    FIRST_PRODUCT_OFFER_DATE = source.FIRST_PRODUCT_OFFER_DATE ,
    FLAG_AT_ONCE = source.FLAG_AT_ONCE ,
    FLAG_BGRADE = source.FLAG_BGRADE ,
    FLAG_CLOSEOUTS = source.FLAG_CLOSEOUTS ,
    FLAG_FUTURES = source.FLAG_FUTURES ,
    FLAG_PARTNERS = source.FLAG_PARTNERS ,
    FLAG_STRATEGIC = source.FLAG_STRATEGIC ,
    MARKETING_CODE = source.MARKETING_CODE ,
    LAUNCH_INDICATOR = source.LAUNCH_INDICATOR ,
    CRDNTD_RLSE_CODE = source.CRDNTD_RLSE_CODE ,
    CUST_ORDR_NUM_CODE = source.CUST_ORDR_NUM_CODE ,
    CMPLTNSS_FLAG = source.CMPLTNSS_FLAG ,
    SLS_OFFICE = source.SLS_OFFICE ,
    CNTRCT_START_DATE = source.CNTRCT_START_DATE ,
    CNTRCT_END_DATE = source.CNTRCT_END_DATE ,
    CNTRCT_DOC_HDR_NUM_CODE = source.CNTRCT_DOC_HDR_NUM_CODE ,
    OB_DLVRY_BLCK_CODE = source.OB_DLVRY_BLCK_CODE ,
    RSRVD_IN_TRANSIT_QTY = source.RSRVD_IN_TRANSIT_QTY ,
    RSRVD_PO_QTY = source.RSRVD_PO_QTY ,
    RSRVD_ON_HAND_QTY = source.RSRVD_ON_HAND_QTY ,
    DELIVERY_PRIORITY = source.DELIVERY_PRIORITY ,
    DLVRY_GRP_CODE = source.DLVRY_GRP_CODE ,
    MATRL_SIZE_CODE = source.MATRL_SIZE_CODE ,
    SO_DLVRY_BLCK_CODE = source.SO_DLVRY_BLCK_CODE ,
    SO_LN_DLVRY_BLCK_CODE = source.SO_LN_DLVRY_BLCK_CODE ,
    RTE_CODE = source.RTE_CODE ,
    SHPPNG_COND = source.SHPPNG_COND ,
    LDNG_PNT_LOCTN = source.LDNG_PNT_LOCTN ,
    ZZZ_RCRD_CREATE_DTTM = source.ZZZ_RCRD_CREATE_DTTM ,
    IDP_DATE = source.IDP_DATE ,
    IDP_FLAG = IDP_FLAG
    WHEN NOT MATCHED THEN
    INSERT(KEY_SLS_ORDR_FACT_ID, KEY_DLVRY_FACT_ID, SOLD_TO_CUST, REGION, SUBRGN, SUBRGN_DESC,
    ACCT_CUST_GRP_CODE, ACCT_SPLY_CHN_GRP_CODE, SLS_ORG_CODE,
    SLS_ORG_DESC, PLNT_LOCTN_CODE, DIVISION,
    SLS_DOC_HDR_NUM_CODE, SLS_DOC_LN_ITM_NUM_CODE,
    SLS_DOC_SCHDL_LN_NUM_CODE, DLVRY_DOC_HDR_NUM_CODE,
    DLVRY_DOC_SCHDL_LN_NUM_CODE, KEY_CNCL_DATE_DIM_ID,
    SHP_TO_CUST, SHP_TO_COUNTRY, MATRL, KEY_CCD_DATE_DIM_ID,
    BUCKET_MONTH_DATE, KEY_CRD_DATE_DIM_ID, KEY_MAD_DIM_ID,
    DATE_OF_DLVR_DUE, CANCEL_DATE, AGID_DATE, PGID_DATE,
    SO_IDP_DATE, SCHDLD_BKNG_IN_DATE, PLND_SCHDLD_DT,
    OUTGOING_DT, AFS_CAT_CODE, CRDT_STAT_CODE, SO_ITM_CAT_CODE,
    SO_IDP_FLAG, DLVRY_CNT, ORDR_ENTRY_QTY, UNCNFRMD_QTY,
    CNFRMD_QTY, BAS_DLVRY_QTY, RSRVD_UNIT_QTY, OPEN_QTY,
    UNASSIGNED_QTY, ALLOC_F_QTY, ALLOC_T_QTY, DLVRY_UNIT_QTY,
    RJCTD_UNIT_QTY, UOM_CNVRSN_FCTR, SOS_BAS_UOM, CAT_CD,
    CHNL_CLASS, LIFECYCLE, SUB_CAT_CODE, TYPE_GRP, ACCT_TYPE,
    ACCT_TYPE_DESC, DIRECT_SHIP_FLAG, QUALITY_CD, ALLOC_D_QTY,
    SCHED_SC_QTY, SCHED_PH_QTY, CONTRACT_QTY, CONTRACT_FLAG,
    CREDIT_FLAG, DIST_METHOD, ALLOC_FB_QTY, ALLOC_FNB_QTY,
    EMEA_RPT_GRP, HOLD_TYPE, DLVRY_HOLD_QTY, ORDR_TYPE_CODE,
    CREDIT_HOLD_QTY, SHIP_ALLOC_QTY, SHIP_IN_PROCESS_QTY,
    FIRST_PRODUCT_OFFER_DATE, FLAG_AT_ONCE, FLAG_BGRADE,
    FLAG_CLOSEOUTS, FLAG_FUTURES, FLAG_PARTNERS, FLAG_STRATEGIC,
    MARKETING_CODE, LAUNCH_INDICATOR, CRDNTD_RLSE_CODE,
    CUST_ORDR_NUM_CODE, CMPLTNSS_FLAG, SLS_OFFICE,
    CNTRCT_START_DATE, CNTRCT_END_DATE, CNTRCT_DOC_HDR_NUM_CODE,
    OB_DLVRY_BLCK_CODE, RSRVD_IN_TRANSIT_QTY, RSRVD_PO_QTY,
    RSRVD_ON_HAND_QTY, DELIVERY_PRIORITY, DLVRY_GRP_CODE,
    MATRL_SIZE_CODE, SO_DLVRY_BLCK_CODE, SO_LN_DLVRY_BLCK_CODE,
    RTE_CODE, SHPPNG_COND, LDNG_PNT_LOCTN, ZZZ_RCRD_CREATE_DTTM,
    IDP_DATE, IDP_FLAG)
    VALUES(
    source.KEY_SLS_ORDR_FACT_ID, ....);

  2. #2
    Join Date
    Dec 2002
    Posts
    134

    Re: How to improve "MERGE INTO" query performance ?

    1. Make sure you have composite index on join columns for the historical table (you can also try creating the same index on a delta table)
    2. Make sure statistics are up to date on both tables.
    3. Post an access plan

    Considering the amount of data, I would assume that the best access plan would be somewhere in
    1. table scan over the delta and netsted loops index lookups for history table
    2. index scan over composite keys(depends on cluster ratio of the key) and merge join
    3. hash join (depends on resources and memory parameters), least likely

  3. #3
    Join Date
    Dec 2002
    Posts
    58

    Re: How to improve "MERGE INTO" query performance ?

    Hello,

    Thanks for the response. Here is the access plan. Hope this helps you to give me some advice .....
    I cut a whole part out in the middle. If you can help me more with the whole file, can you please give me your e-mail because here I can only paste 10000 chars

    Plan:
    -----------
    Total Cost: 2.63619e+007
    Query Degree: 0

    Rows
    RETURN
    ( 1)
    Cost
    I/O
    |
    15102
    INSERT
    ( 2)
    2.63619e+007
    5.63197e+006
    /----+----\
    15102 3.27545e+007
    TBSCAN TABLE: DIFOT
    ( 3) EMEA_FEED_HIST
    2.63538e+007
    5.63122e+006
    |
    15102
    TEMP
    ( 4)
    2.63512e+007
    5.62933e+006
    |
    15102
    UPDATE
    ( 5)
    2.6331e+007
    5.62744e+006
    /----+----\
    15102 3.27545e+007
    FETCH TABLE: DIFOT
    ( 6) EMEA_FEED_HIST
    2.61692e+007
    5.61234e+006
    /----+----\
    15102 3.27545e+007
    NLJOIN TABLE: DIFOT
    ( 7) EMEA_FEED_HIST
    2.60074e+007
    5.59724e+006
    /---------+---------\
    188775 0.08
    FILTER UNION
    ( 8) ( 19)
    2.60071e+007 0.00107591
    5.59724e+006 0
    | /------+-----\
    4.71938e+006 0.04 0.04
    TBSCAN FILTER FILTER
    ( 9) ( 20) ( 22)
    2.6002e+007 0.000428652 0.000428652
    5.59724e+006 0 0
    | | |
    4.71938e+006 1 1
    SORT TBSCAN TBSCAN
    ( 10) ( 21) ( 23)
    2.49746e+007 2.83407e-005 2.83407e-005
    4.85207e+006 0 0
    | | |
    4.71938e+006 1 1
    MSJOIN TABFNC: SYSIBM TABFNC: SYSIBM
    ( 11) GENROW GENROW
    1.69621e+007
    4.1069e+006
    /-----+----\
    4.71938e+006 7.54043e-006
    TBSCAN FILTER
    ( 12) ( 15)
    8.91488e+006 6.28638e+006
    1.65215e+006 2.29182e+006
    | |
    4.71938e+006 3.27545e+007
    SORT TBSCAN
    ( 13) ( 16)
    7.93902e+006 6.28023e+006
    944246 2.29182e+006
    | |
    4.71938e+006 3.27545e+007
    TBSCAN SORT
    ( 14) ( 17)
    326941 5.81384e+006
    236339 1.96591e+006
    | |
    4.71938e+006 3.27545e+007
    TABLE: DIFOT TBSCAN
    EMEA_LOAD ( 18)
    2.26865e+006
    1.63999e+006
    |
    3.27545e+007
    TABLE: DIFOT
    EMEA_FEED_HIST



    ..
    ..
    ..


    Objects Used in Access Plan:
    ---------------------------

    Schema: DIFOT
    Name: EMEA_FEED_HIST
    Type: Table
    Time of creation: 2004-02-01-06.37.54.546000
    Last statistics update: 2004-02-04-11.29.08.796000
    Number of columns: 107
    Number of rows: 32754501
    Width of rows: 787
    Number of buffer pool pages: 1639992
    Distinct row values: No
    Tablespace name: DIFOT
    Tablespace overhead: 10.670000
    Tablespace transfer rate: 0.040000
    Source for statistics: Single Node
    Prefetch page count: 64
    Container extent page count: 64
    Table overflow record count: 0
    Table Active Blocks: -1

    Schema: DIFOT
    Name: EMEA_LOAD
    Type: Table
    Time of creation: 2004-02-03-10.51.09.846001
    Last statistics update: 2004-02-04-11.01.34.155000
    Number of columns: 107
    Number of rows: 4719380
    Width of rows: 786
    Number of buffer pool pages: 236339
    Distinct row values: No
    Tablespace name: DIFOT
    Tablespace overhead: 10.670000
    Tablespace transfer rate: 0.040000
    Source for statistics: Single Node
    Prefetch page count: 64
    Container extent page count: 64
    Table overflow record count: 0
    Table Active Blocks: -1

    Schema: SYSIBM
    Name: GENROW
    Type: Table Function
    Time of creation:
    Last statistics update:
    Number of columns: 1
    Number of rows: 1
    Width of rows: 11
    Number of buffer pool pages: -1
    Distinct row values: No
    Source for statistics: Single Node

  4. #4
    Join Date
    Dec 2002
    Posts
    134

    Re: How to improve "MERGE INTO" query performance ?

    It's really difficult to read such collapsed access plan. Can you create a seperate file and make an attachment?

    Can you create unique indexes on joins keys for both tables and try again?

  5. #5
    Join Date
    Dec 2002
    Posts
    58
    I'm sorry. I didn't realise that attachments were possible.
    Here's the complete explain.

    I will make sure unique keys exist and then re-run the query (and explain)

    Thanks already
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •