Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Aug 2013
    Posts
    80

    Unanswered: Transform SQL statement

    Hi All,
    I found following statement run pretty slow. I am not sure this statement
    can transform it to better statement like analytic function, etc ?
    for information, the referring tables records are more than 5mil.

    Code:
    select IP.IP_ID, AR_ID, IP.NM, AR_X_IP_TP_ID, AR_X_IP.SRC_STM_ID,
    AR_X_IP.END_DT from DSSBDW.IP IP, DSSBDW.AR_X_IP AR_X_IP  
    where IP.IP_ID = AR_X_IP.IP_ID  and AR_X_IP.SRC_STM_ID = 'SIB' and AR_X_IP.BK_CODE = '035'   GROUP
    BY IP.IP_ID, AR_ID, IP.NM, AR_X_IP_TP_ID, AR_X_IP.SRC_STM_ID, AR_X_IP.END_DT
    Appreciate if someone can share better optimized sql statement.

    Thanks.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    if no details supplied about table ddl and indexes and stats.... nobody can help you..
    we can just guess..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    Have you tried to run Design Advisor on this query?
    Regards,
    Mark.

  4. #4
    Join Date
    Aug 2013
    Posts
    80
    actually I had ran explain plan for the query in 2 different server, surprisingly it return different cost. Server A run faster compare to Server B. But all the indexes and runstats are all up to date and similar. So I try to run db2advisor in Server B, it come out the recommendation to add 1 index and the cost very much reduced. For your information, Server A (32G RAM and 5 CPU) hardware specs are different from Server B (64G RAM, 4CPU).

    The reason I post this is I want to see if there are other way of transform this SQL statement maybe using analytic function approach to optimized this statement as I see these are large tables and there are group by many columns.

  5. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    What you can do to improve the performance of this query further is to create MQT. Even with refresh IMMEDIATE option according to the type of this query.
    If you can show the DDL of both tables, then I can try to show you exact CREATE TABLE statement.
    Last edited by mark.b; 08-29-13 at 03:46.
    Regards,
    Mark.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (1) Are there appropriate indexes?

    (2) Are there any unique column(or combination of columns)?

    I guessed that IP.IP_ID might be unique from the name.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If there were some uniqueness (and referential integrity),
    some transformation using the uniqueness (and referential integrity) might be possible.
    Last edited by tonkuma; 08-29-13 at 05:10. Reason: Add "(and referential integrity)"

  8. #8
    Join Date
    Aug 2013
    Posts
    80
    Create table DSSBDW.IP (
    IP_ID INTEGER NOT NULL ,
    UNQ_ID_SRC_STM CHARACTER(32) ,
    DUP_IP_ID INTEGER ,
    IP_TP_ID CHARACTER(6) NOT NULL ,
    PRIM_RLN_TP_ID CHARACTER(6) ,
    CST_LCS_TP_ID CHARACTER(6) ,
    CST_LCS_TP_EFF_DT DATE ,
    NM VARCHAR(255) ,
    ADVG_F CHARACTER(6) NOT NULL ,
    SRC_STM_PRTY DECIMAL(4,2) ,
    PREV_RFS_F CHARACTER(6) ,
    BLIST_F CHARACTER(6) ,
    EFF_DT DATE ,
    END_DT DATE ,
    DSC VARCHAR(256) ,
    CPY_CAT_CODE CHARACTER(6) ,
    STE_CODE CHARACTER(6) ,
    IP_SHRT_NM VARCHAR(64) ,
    CTRY_CODE CHARACTER(6) ,
    REF_NO VARCHAR(50) ,
    ROW_NO INTEGER ,
    SRC_STM_ID CHARACTER(6) ,
    SUSP_RSN_CODE CHARACTER(6) ,
    BRPT_RSN_CODE CHARACTER(6) ,
    FNC_LGL_ST_ID CHARACTER(6) ,
    FNC_LGL_ST_EFF_DT DATE ,
    PPN_TM TIMESTAMP ,
    SRC_IP_ID INTEGER ,
    OU_BU_CAT CHARACTER(10) ,
    OU_BU_ID CHARACTER(10) ,
    CUST_SEG CHARACTER(6) ,
    ADR_FLAG CHARACTER(6) ,
    CUST_CLASS CHARACTER(6) ,
    DEALER_IND SMALLINT ,
    IP_ID_TP_ID CHARACTER(6) ,
    IP_GSAM CHARACTER(6) ,
    BK_CODE VARCHAR(3) ,
    SCHEME_CODE CHARACTER(6) ,
    SCHEME_DATE DATE ,
    LP_IND VARCHAR(1) ,
    LP_START_DATE DATE ,
    LP_END_DATE DATE ,
    LP_TC_DATE DATE ,
    LP_TC_BRANCH VARCHAR(4) ,
    LP_TERMINATOR VARCHAR(1) ,
    NM2 VARCHAR(40) ,
    TELLER_ID VARCHAR(10) ,
    STATEMENT_FLAG CHARACTER(6) ,
    GST_REG_IND VARCHAR(1) ,
    BRPT_RSN_DESC VARCHAR(255) ,
    LP_TP_ID CHARACTER(6) )
    in ASCV_SMY_TBS Index in IDX_TBS
    Partitioning Key (IP_ID) Using Hashing
    Compress Yes
    Value Compression;
    alter table DSSBDW.IP PctFree 15;

    create Index DSSBDW.I1IP
    on DSSBDW.IP
    (IP_ID, UNQ_ID_SRC_STM, IP_TP_ID, SRC_STM_ID) Allow Reverse Scans;

    create Index DSSBDW.I2IP
    on DSSBDW.IP
    (IP_TP_ID, CPY_CAT_CODE) Allow Reverse Scans;


    create Index DSSBDW.IDX_IP_DUPIP
    on DSSBDW.IP
    (IP_ID, CST_LCS_TP_ID, END_DT) Allow Reverse Scans;


    create Index DSSBDW.IIP_CODE
    on DSSBDW.IP
    (CPY_CAT_CODE, IP_ID, IP_TP_ID) Allow Reverse Scans;

    create Index DSSBDW.IIP_DUPIP
    on DSSBDW.IP
    (IP_ID, DUP_IP_ID) Allow Reverse Scans;

    create Index DSSBDW.IIP_OUBU
    on DSSBDW.IP
    (OU_BU_ID, IP_ID) Allow Reverse Scans;


    create Index DSSBDW.IP
    on DSSBDW.IP
    (IP_ID) Allow Reverse Scans;

    create Index DSSBDW.IP_DEDUP1
    on DSSBDW.IP
    (SRC_IP_ID) Allow Reverse Scans;

    create Index DSSBDW.IP_I1
    on DSSBDW.IP
    (SRC_STM_ID, REF_NO, CST_LCS_TP_ID) Allow Reverse Scans;

    create Index DSSBDW.IP_I2
    on DSSBDW.IP
    (IP_ID, UNQ_ID_SRC_STM, SRC_STM_ID, REF_NO) Allow Reverse Scans;

    create Index DSSBDW.IP_IDX17
    on DSSBDW.IP
    (IP_ID, NM) Allow Reverse Scans;

    create Index DSSBDW.IP_IX7
    on DSSBDW.IP
    (END_DT, CST_LCS_TP_ID, IP_ID) Allow Reverse Scans;

    create Index DSSBDW.IP_SR0133_1
    on DSSBDW.IP
    (UNQ_ID_SRC_STM, IP_ID, CUST_SEG) Allow Reverse Scans;

    --------------------------------------------------
    -- Create Index DSSBDW.IP_SR0133_2
    --------------------------------------------------
    create Index DSSBDW.IP_SR0133_2
    on DSSBDW.IP
    (SRC_STM_ID) Allow Reverse Scans;

    create Index DSSBDW.IP_TEMP_1
    on DSSBDW.IP
    (UNQ_ID_SRC_STM) Allow Reverse Scans;

    create Index DSSBDW.IP_TEMP_2
    on DSSBDW.IP
    (REF_NO) Allow Reverse Scans;

    create Index DSSBDW.T1IP
    on DSSBDW.IP
    (CPY_CAT_CODE) Allow Reverse Scans;

    --------------------------------------------------
    -- Create Index DSSBDW.XIE1IP
    --------------------------------------------------
    create Index DSSBDW.XIE1IP
    on DSSBDW.IP
    (UNQ_ID_SRC_STM, SRC_STM_ID) Allow Reverse Scans;


    ************************************************** ********

    Create table DSSBDW.AR_X_IP (
    AR_ID INTEGER NOT NULL ,
    IP_ID INTEGER NOT NULL ,
    AR_X_IP_TP_ID CHARACTER(6) NOT NULL ,
    RANK SMALLINT NOT NULL ,
    EFF_DT DATE NOT NULL ,
    END_DT DATE ,
    REF_NO VARCHAR(50) ,
    ROW_NO INTEGER ,
    SRC_STM_ID CHARACTER(6) ,
    PPN_TM TIMESTAMP ,
    AR_X_IP_KEY CHARACTER(12) ,
    AR_X_IP_NO INTEGER ,
    AR_X_IP_STATUS CHARACTER(6) ,
    BK_CODE VARCHAR(3) ,
    AR_X_IP_PRIN VARCHAR(35) ,
    OLD_KEY CHARACTER(12) ,
    OLD_NO INTEGER ,
    GROUP_IND VARCHAR(1) )
    in ASCV_SMY_TBS
    Partitioning Key (AR_ID) Using Hashing
    Compress Yes;
    alter table DSSBDW.AR_X_IP PctFree 15;

    create Index DSSBDW.AR_X_IP_ALZ
    on DSSBDW.AR_X_IP
    (AR_ID, SRC_STM_ID) Allow Reverse Scans;

    create Index DSSBDW.AR_X_IP_PTN
    on DSSBDW.AR_X_IP
    (AR_X_IP_TP_ID, RANK, EFF_DT) Allow Reverse Scans;

    create Index DSSBDW.AR_X_IP_RCS_2_IP
    on DSSBDW.AR_X_IP
    (IP_ID) Allow Reverse Scans;

    create Index DSSBDW.AR_X_IP_TEMP_1
    on DSSBDW.AR_X_IP
    (AR_X_IP_TP_ID, IP_ID, AR_ID) Allow Reverse Scans;

    create Index DSSBDW.AR_XIP_IDX_1
    on DSSBDW.AR_X_IP
    (END_DT, AR_ID, AR_X_IP_TP_ID) Allow Reverse Scans;

    create Index DSSBDW.AR_XIP_IDX_2
    on DSSBDW.AR_X_IP
    (AR_ID, IP_ID) Allow Reverse Scans;

    create Index DSSBDW.ARXIP_REL1
    on DSSBDW.AR_X_IP
    (IP_ID, PPN_TM, SRC_STM_ID, REF_NO, END_DT, EFF_DT, AR_X_IP_TP_ID) Allow Reverse Scans;

    create Index DSSBDW.IDX_ARXIP1
    on DSSBDW.AR_X_IP
    (AR_ID) Allow Reverse Scans;

    create Index DSSBDW.IDX1307040007030
    on DSSBDW.AR_X_IP
    (SRC_STM_ID, BK_CODE, END_DT, AR_X_IP_TP_ID, AR_ID, IP_ID) Allow Reverse Scans;

    create Index DSSBDW.IDX1307121611190
    on DSSBDW.AR_X_IP
    (AR_X_IP_TP_ID, SRC_STM_ID, AR_ID) Allow Reverse Scans;
    Above are the two tables structures and indexes.

    I checked the DSSBDW.IP IP_ID column is NOT UNIQUE KEY

    similarly to DSSBDW.AR_X_IP AR_ID, IP_ID also is not the UNIQUE KEY

    Basically there's no uniqueness for both tables. It only contain normal index shown above.

    Thanks for all your time.

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Your tables are partitioned using different keys, so the join is not collocated. Depending on the table sizes, this can be quite inefficient. Please show the explain plan.
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    What's the cardinality (number of rows) of both tables?
    Regards,
    Mark.

  11. #11
    Join Date
    Aug 2013
    Posts
    80
    select IP.IP_ID, AR_ID, IP.NM, AR_X_IP_TP_ID, AR_X_IP.SRC_STM_ID,
    AR_X_IP.END_DT
    from DSSBDW.IP IP, DSSBDW.AR_X_IP AR_X_IP
    where IP.IP_ID =AR_X_IP.IP_ID and AR_X_IP.SRC_STM_ID ='SIB' and
    AR_X_IP.BK_CODE ='035'
    GROUP BY IP.IP_ID, AR_ID, IP.NM, AR_X_IP_TP_ID, AR_X_IP.SRC_STM_ID,
    AR_X_IP.END_DT


    Section Code Page = 819

    Estimated Cost = 98862.445312
    Estimated Cardinality = 8937443.000000

    Coordinator Subsection - Main Processing:
    Distribute Subsection #1
    | Broadcast to Node List
    | | Nodes = 1, 2, 3, 4, 5, 6, 7
    Distribute Subsection #2
    | Broadcast to Node List
    | | Nodes = 1, 2, 3, 4, 5, 6, 7
    Access Table Queue ID () = q1 #Columns = 6
    Return Data to Application
    | #Columns = 6

    Subsection #1:
    Access Table Queue ID () = q2 #Columns = 4
    Hash Join
    | Estimated Build Size: 66768000
    | Estimated Probe Size: 999104000
    | Bit Filter Size: 1261152
    | Access Table Name = DSSBDW.IP ID = 10,97
    | | #Columns = 2
    | | Compressed Table
    | | Skip Inserted Rows
    | | Avoid Locking Committed Data
    | | Currently Committed for Cursor Stability
    | | May participate in Scan Sharing structures
    | | Scan may start anywhere and wrap, for completion
    | | Fast scan, for purposes of scan sharing management
    | | Scan can be throttled in scan sharing management
    | | Relation Scan
    | | | Prefetch: Eligible
    | | Lock Intents
    | | | Table: Intent Share
    | | | Row : Next Key Share
    | | Sargable Predicate(s)
    | | | Process Probe Table for Hash Join
    Insert Into Sorted Temp Table ID = t1
    | #Columns = 5
    | #Sort Key Columns = 5
    | | Key 1: IP_ID (Ascending)
    | | Key 2: (Ascending)
    | | Key 3: NM (Ascending)
    | | Key 4: (Ascending)
    | | Key 5: (Ascending)
    | Sortheap Allocation Parameters:
    | | #Rows = 1276778.000000
    | | Row Width = 172
    | Piped
    Access Temp Table ID = t1
    | #Columns = 5
    | Relation Scan
    | | Prefetch: Eligible
    | Sargable Predicate(s)
    | | Predicate Aggregation
    | | | Group By
    Aggregation Completion
    | Group By
    Insert Into Asynchronous Table Queue ID () = q1
    | Broadcast to Coordinator Node
    | Rows Can Overflow to Temporary Table

    Subsection #2:
    Access Table Name = DSSBDW.AR_X_IP ID = 10,32
    | Index Scan: Name = RICHARD.IDX1307040007030 ID = 9
    | | Regular Index (Not Clustered)
    | | Index Columns:
    | | | 1: SRC_STM_ID (Ascending)
    | | | 2: BK_CODE (Ascending)
    | | | 3: END_DT (Ascending)
    | | | 4: AR_X_IP_TP_ID (Ascending)
    | | | 5: AR_ID (Ascending)
    | | | 6: IP_ID (Ascending)
    | #Columns = 6
    | Compressed Table
    | Skip Inserted Rows
    | Avoid Locking Committed Data
    | Currently Committed for Cursor Stability
    | #Key Columns = 2
    | | Start Key: Inclusive Value
    | | | | 1: 'SIB '
    | | | | 2: '035'
    | | Stop Key: Inclusive Value
    | | | | 1: 'SIB '
    | | | | 2: '035'
    | Index-Only Access
    | Index Prefetch: Eligible 830
    | Lock Intents
    | | Table: Intent Share
    | | Row : Next Key Share
    | Sargable Index Predicate(s)
    | | Insert Into Asynchronous Table Queue ID () = q2
    | | | Hash to Specific Node
    | | | Rows Can Overflow to Temporary Tables
    Insert Into Asynchronous Table Queue Completion () ID = q2

    End of section


    Optimizer Plan:

    Rows
    Operator
    (ID)
    Cost

    8.93744e+06
    n/a
    RETURN
    ( 1)
    98862.4
    |
    8.93744e+06
    n/a
    BTQ
    ( 2)
    98862.4
    |
    1.27678e+06
    n/a
    GRPBY
    ( 3)
    95971.9
    |
    1.27678e+06
    n/a
    TBSCAN
    ( 4)
    95894
    |
    1.27678e+06
    n/a
    SORT
    ( 5)
    88898.4
    |
    1.27678e+06
    n/a
    HSJOIN
    ( 6)
    63701.4
    / \
    6.1819e+06 1.27677e+06
    n/a n/a
    TBSCAN DTQ
    ( 7) ( 8)
    60326.7 2821.38
    | |
    6.1819e+06 1.27677e+06
    n/a n/a
    Table: IXSCAN
    DSSBDW ( 9)
    IP 1998.87
    |
    7.69376e+06
    Index:
    RICHARD
    IDX1307040007030
    Above are the Explain Plan I generated from the sql statement

  12. #12
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    The first possible solution is to create an MQT:

    Code:
    CREATE TABLE DSSBDW.IP_MQT AS (
    select IP.IP_ID, AR_ID, IP.NM, AR_X_IP_TP_ID, AR_X_IP.SRC_STM_ID, AR_X_IP.END_DT 
    , AR_X_IP.BK_CODE
    , COUNT_BIG(*) CNT
    from DSSBDW.IP IP, DSSBDW.AR_X_IP AR_X_IP  
    where IP.IP_ID = AR_X_IP.IP_ID  
    GROUP BY IP.IP_ID, AR_ID, IP.NM, AR_X_IP_TP_ID, AR_X_IP.SRC_STM_ID, AR_X_IP.END_DT, AR_X_IP.BK_CODE
    )
    DATA INITIALLY DEFERRED
    REFRESH IMMEDIATE
    IN nonpartitioned_tablespace;
    
    CREATE INDEX "DB2ADMIN"."IDX1308291412010" ON "DSSBDW  "."IP_MQT"
    ("SRC_STM_ID" ASC, "BK_CODE" ASC, "IP_ID" ASC, "AR_ID"   ASC, "NM" ASC, "AR_X_IP_TP_ID" ASC, "END_DT" ASC);
    
    REFRESH TABLE DSSBDW.IP_MQT;
    
    RUNSTATS ON TABLE DSSBDW.IP_MQT AND INDEXES ALL;
    But you should check you update/delete/insert statements on both tables since this MQT can decrease the performance of these operations.
    If the impact is significant, then consider the REFRESH DEFERRED option of this MQT with a staging table.
    Regards,
    Mark.

  13. #13
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Apart from what Mark suggests, consider the possibility of partitioning AR_X_IP by IP_ID instead of AR_ID.
    ---
    "It does not work" is not a valid problem statement.

  14. #14
    Join Date
    Aug 2013
    Posts
    80
    For your information, the DSSBDW.IP table card is about 43Mil while DSSBDW.AR_X_IP contains 53mil. Anyway, I will give a try to create MQT table. Thanks.

  15. #15
    Join Date
    Aug 2013
    Posts
    80
    Hi Mark, any specific reason the MQT table has to create in single partition tables ?

Posting Permissions

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