Results 1 to 7 of 7

Thread: Query Tuning

  1. #1
    Join Date
    Oct 2008
    Posts
    82

    Unanswered: Query Tuning

    HI Everyone, We are using DB2 9.7,RED HAT LINUX

    One of the query is taking more than one hour time .. Can anyone please let me know the modification in below query for better performance,

    SELECT PPTY_VW.PROPERTY_CODE AS HOTEL_KEY,
    RESV.CONFIRMATION_NUM AS CONFIRMATION_NUMBER,
    TO_NUMBER(RESV.CONFIRMATION_PAGE) AS PAGE_NUMBER,
    RESV.RESERVATION_DATE AS DATE_OF_ENTRY,
    RESV.ENTRY_TIME AS ENTRY_TIME,
    RESV.ARRIVAL_DATE AS DATE_OF_ARRIVAL,
    RESV.DEPARTURE_DATE AS DATE_OR_DEPARTURE,
    RESV.RESERVATION_STATUS AS RESEVATION_STATUS,
    CASE WHEN RESV.ARRIVAL_FLG IS NULL OR RESV.ARRIVAL_FLG = 0 THEN NULL ELSE RESV.ARRIVAL_FLG END AS ARRIVAL_FLAG,
    RESV_RM.RTYP_CD AS ROOM_TYPE_CODE,
    RESV.NUM_ROOMS AS NUMBER_OF_ROOMS,
    CASE WHEN RESV.ADULT_NUM IS NULL OR RESV.ADULT_NUM = 0 THEN NULL ELSE RESV.ADULT_NUM END AS NUMMBER_OF_ADULTS,
    CASE WHEN RESV.CHILDREN_NUM IS NULL OR RESV.CHILDREN_NUM = 0 THEN NULL ELSE RESV.CHILDREN_NUM END AS NUMBER_OF_CHILDREN,
    RT_PLAN.RATE_PLAN AS RATE_PLAN_CODE,
    MRKT.MARKET_CODE AS MARKET_CODE,
    RT_CTGY.RATE_CATEGORY_CODE AS RATE_CATEGORY_CODE,
    CASE WHEN (RESV.REVENUE_USD = 0.0 OR RESV.REVENUE_USD IS NULL) THEN NULL
    WHEN ((RESV.DIRECT_NIGHTS_NUM IS NULL OR RESV.DIRECT_NIGHTS_NUM = 0) AND RESV.REVENUE_USD > 0.0 ) THEN TO_CHAR(0)
    ELSE TO_CHAR(CAST(ROUND((RESV.REVENUE_USD / (RESV.DIRECT_NIGHTS_NUM * RESV.NUM_ROOMS)), 2) AS DECIMAL(15,2))) END AS HOTEL_ADR,
    RESV.CURRENCY_CODE AS CURRENCY_CODE,
    RESV.LAST_CHANGE_DATE AS LAST_UPDATE_DATE,
    RESV.CHANNEL_ID AS DIST_CHANNEL_KEY,
    BK_SEG_SRC.SRC_NUM AS CRNUM
    FROM PROPERTY_VIEW PPTY_VW,
    CONTROL_EFLEX_OUTBOUND CTRL,
    RESERVATION_ROOM RESV_RM,
    RATE_PLAN RT_PLAN,
    MARKET MRKT,
    RATE_CATEGORY RT_CTGY,
    RESERVATION RESV
    FULL OUTER JOIN
    ( SELECT CNF_NUM AS CNF_NUM,
    MIN(PG_NUM) AS PG_NUM,
    MIN(ORDER_NUM) AS ORDER_NUM
    FROM STG_SPIRIT_BOOK_SEG_SRC
    GROUP BY CNF_NUM) T
    ON RESV.CONFIRMATION_NUM = T.CNF_NUM
    LEFT OUTER JOIN
    STG_SPIRIT_BOOK_SEG_SRC BK_SEG_SRC
    ON (RESV.CONFIRMATION_NUM = T.CNF_NUM
    AND T.CNF_NUM = BK_SEG_SRC.CNF_NUM
    AND T.PG_NUM = BK_SEG_SRC.PG_NUM
    AND T.ORDER_NUM = BK_SEG_SRC.ORDER_NUM)
    WHERE PPTY_VW.SOURCE_SYSTEM_ID = CTRL.PROPERTY_ID
    AND RESV.ARRIVAL_DATE > CTRL.EXTRACT_DATE
    AND RESV.LAST_CHANGE_DATE > CTRL.EXTRACT_DATE
    AND RESV.LAST_CHANGE_DATE <= TO_DATE(TO_CHAR(SYSDATE - 1, 'MM/DD/YYYY'), 'MM/DD/YYYY')
    AND RESV.PROPERTY_ID = PPTY_VW.PROPERTY_ID
    AND RESV.RESERVATION_ROOM_ID = RESV_RM.RESERVATION_ROOM_ID
    AND RESV.RATE_PLAN_ID = RT_PLAN.RATE_PLAN_ID
    AND RESV.MARKET_ID = MRKT.MARKET_ID
    AND RESV.RATE_CATEGORY_ID = RT_CTGY.RATE_CATEGORY_ID
    ORDER BY PPTY_VW.PROPERTY_CODE, RESV.CONFIRMATION_NUM, RESV.CONFIRMATION_PAGE

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What were the DDLs(CREATE TABLE statements, CREATE INDEX statements, so on...) of the tables?

    How many rows in each table?

    Are you using Oracle compatibility feature?
    (Because, "SYSDATE - 1" in your query might not work on DB2 without using the Oracle compatibility feature.)

    What was the result of db2exfmt?
    (not necessary all of the output. The diagram might be enough at the first time.)

  3. #3
    Join Date
    Oct 2008
    Posts
    82
    Thanks for replying

    Only reservation table is having 100 million records and remaining all tables have less data

    Please find the graph below..
    Attached Files Attached Files

  4. #4
    Join Date
    Oct 2008
    Posts
    82
    I am not sure about oracle compatibility but sysdate is working .. we are using advanced Enterprice server edition

    $ db2 "select sysdate-1 from sysibm.sysdummy1"

    1
    -------------------
    2013-12-23-14.52.13

    1 record(s) selected.

  5. #5
    Join Date
    Nov 2011
    Posts
    334
    hi, rameshj6
    FULL OUTER JOIN (SELECT CNF_NUM AS CNF_NUM,
    Min(PG_NUM) AS PG_NUM,
    Min(ORDER_NUM) AS ORDER_NUM
    FROM STG_SPIRIT_BOOK_SEG_SRC
    GROUP BY CNF_NUM) T
    ON RESV.CONFIRMATION_NUM = T.CNF_NUM
    It seems these full outer join above is not neccessary, there is no reference to the table t in your select column list。

    and you can try the following advise:
    1、add index ( RATE_PLAN_ID,RATE_PLAN ) to table RATE_PLAN.
    2、add index ( RATE_CATEGORY_ID,RATE_CATEGORY_CODE) to table RATE_CATEGORY.
    3、change table RESERVATION to data partitioned table ,using LAST_CHANGE_DATE as partitioned column.
    thx

  6. #6
    Join Date
    Oct 2008
    Posts
    82
    Thanks much ..

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    One item on the removal of your full outer join. The table is necessary as it is being used to find the appropriate row in STG_SPIRIT_BOOK_SEG_SRC. Though, I do not think it should be a full outer join as in the LOJ you are equating back to the RESV table. Perhaps, you could change this into a single nested expression that you outer join to.
    Something along the lines of:
    Code:
    LEFT OUTER JOIN table
    ( SELECT min(PG_NUM), min(ORDER_NUM), columns_you_need_from_STG_SPIRIT_BOOK_SEG_SRC_table
         from STG_SPIRIT_BOOK_SEG_SRC BK_SEG_SRC 
    where RESV.CONFIRMATION_NUM = BK_SEG_SRC.CNF_NUM
    group by columns_you_need_from_STG_SPIRIT_BOOK_SEG_SRC_table) as T
    ON RESV.CONFIRMATION_NUM = T.CNF_NUM

Posting Permissions

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