Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2009
    Posts
    2

    Smile Unanswered: find issues with query & Improve (tune) sql query for better performance



    Can anyone let know how is the below query is efficient because its creating slow performance issues and also good to inform about improve the sql query found below(tuning)
    INSERT INTO VHUPC
    SELECT G.TRANS_DATE, G.UPC, G.DEPT_NUM, G.STROKE_NUM
    , G.COLOUR_CODE, G.PRIM_SIZE_DESC
    , G.SEC_SIZE_DESC, G.RETAIL_PRICE
    , G.COST_PRICE, G.ORIG_UK_PRICE, G.TAX_CODE
    , CASE WHEN F.UPC IS NOT NULL
    THEN F.DECL_AVAIL_SGLS ELSE G.DECL_AVAIL_SGLS END
    , CASE WHEN F.UPC IS NOT NULL
    THEN F.FREE_AVAIL_SGLS ELSE G.FREE_AVAIL_SGLS END
    , CASE WHEN F.UPC IS NOT NULL
    THEN F.ORDERED_SGLS ELSE G.ORDERED_SGLS END
    , CASE WHEN F.UPC IS NOT NULL
    THEN F.RESERVED_SGLS ELSE G.RESERVED_SGLS END
    , G.CNCT_SOURCE
    , G.CNCT_PAYMENT
    , G.CNCT_DELIVERY
    , G.REDUCED_IND
    , CASE WHEN H.UPC IS NOT NULL
    THEN H.BOOKED_IN_SGLS ELSE 0 END
    FROM VHUPCT1 G LEFT OUTER JOIN
    (SELECT E.UPC AS UPC, E.RND_DECLD_AV_QTY AS DECL_AVAIL_SGLS
    , E.RND_FREE_AVAIL_S AS FREE_AVAIL_SGLS
    , CASE WHEN D.UPC IS NOT NULL
    THEN D.ORDERED_QTY ELSE 0 END AS ORDERED_SGLS
    , CASE WHEN D.UPC IS NOT NULL
    THEN D.RESERVED_QTY ELSE 0 END AS RESERVED_SGLS
    FROM VNBORD D
    RIGHT OUTER JOIN
    (
    SELECT UPC,
    RND_DECLD_AV_QTY, RND_FREE_AVAIL_S
    FROM PNB001.VDEPT A INNER JOIN VNBWAY B
    ON A.PART_ID=B.PART_ID
    INNER JOIN VSTCOL C
    ON A.DEPARTMENT_NUMBER=C.DEPT_NUM
    AND B.STROKE_NUMBER=C.STROKE_NUM
    AND B.COLOUR_CODE=C.COLOUR_CODE) E ON D.UPC=E.UPC) F
    ON F.UPC = G.UPC
    LEFT OUTER JOIN VBOOKD H ON H.UPC=G.UPC

    table structures are provided below


    VNBORD
    EXEC SQL DECLARE VNBORD TABLE
    ( RF_OWNER_CODE CHAR(2) NOT NULL,
    UPC CHAR(8) NOT NULL,
    ORDERED_QTY INTEGER NOT NULL,
    FULFILLED_QTY INTEGER NOT NULL,
    RESERVED_QTY INTEGER NOT NULL
    ) END-EXEC.
    *******
    01 DCLVNBORD.
    10 RF-OWNER-CODE PIC X(2).
    10 UPC PIC X(8).
    10 ORDERED-QTY PIC S9(9) USAGE COMP.
    10 FULFILLED-QTY PIC S9(9) USAGE COMP.
    10 RESERVED-QTY PIC S9(9) USAGE COMP.
    ------
    EXEC SQL DECLARE DB2DFD1.VSTCOL TABLE
    ( DEPT_NUM CHAR(4) NOT NULL,
    RANGE_CODE CHAR(3) NOT NULL,
    STROKE_NUM CHAR(6) NOT NULL,
    COLOUR_CODE CHAR(3) NOT NULL,
    LAST_UPDATE TIMESTAMP NOT NULL,
    USERID CHAR(8) NOT NULL
    ) END-EXEC.
    **********
    01 DCLVSTCOL.
    10 DEPT-NUM PIC X(4).
    10 RANGE-CODE PIC X(3).
    10 STROKE-NUM PIC X(6).
    10 COLOUR-CODE PIC X(3).
    10 LAST-UPDATE PIC X(26).
    10 USERID PIC X(8).



    EXEC SQL DECLARE PDF001.VHSTCOL TABLE
    ( DEPT_NUM CHAR(4) NOT NULL,
    STROKE_NUM CHAR(5) NOT NULL,
    COLOUR_CODE CHAR(3) NOT NULL,
    CNCT_QTY INTEGER NOT NULL,
    ADDED_DATE DATE NOT NULL,
    DELETED_DATE DATE,
    ARCHIVED_DATE DATE,
    SUSPENDED_DATE DATE,
    SPM_UNCATLG_DATE DATE,
    LAST_USERID CHAR(8) NOT NULL,
    LAST_UPDATE TIMESTAMP NOT NULL,
    USERID CHAR(8) NOT NULL
    ) END-EXEC.
    ***** 01 DCLVHSTCOL.
    10 DEPT-NUM PIC X(4).
    10 STROKE-NUM PIC X(5).
    10 COLOUR-CODE PIC X(3).
    10 CNCT-QTY PIC S9(9) USAGE COMP.
    10 ADDED-DATE PIC X(10).
    10 DELETED-DATE PIC X(10).
    10 ARCHIVED-DATE PIC X(10).
    10 SUSPENDED-DATE PIC X(10).
    10 SPM-UNCATLG-DATE PIC X(10).
    10 LAST-USERID PIC X(8).
    10 LAST-UPDATE PIC X(26).
    10 USERID PIC X(8).
    ***** EXEC SQL DECLARE PDF001.VHUPC TABLE
    ( TRANS_DATE DATE NOT NULL,
    UPC CHAR(8) NOT NULL,
    DEPT_NUM CHAR(4) NOT NULL,
    STROKE_NUM CHAR(5) NOT NULL,
    COLOUR_CODE CHAR(3) NOT NULL,
    PRIM_SIZE_DESC CHAR(5) NOT NULL,
    SEC_SIZE_DESC CHAR(3) NOT NULL,
    RETAIL_PRICE DECIMAL(7, 2) NOT NULL,
    COST_PRICE DECIMAL(7, 2) NOT NULL,
    ORIG_UK_PRICE DECIMAL(7, 2) NOT NULL,
    TAX_CODE CHAR(1) NOT NULL,
    DECL_AVAIL_SGLS INTEGER NOT NULL,
    FREE_AVAIL_SGLS INTEGER NOT NULL,
    ORDERED_SGLS INTEGER NOT NULL,
    RESERVED_SGLS INTEGER NOT NULL,
    CNCT_SOURCE CHAR(1) NOT NULL,
    CNCT_PAYMENT CHAR(1) NOT NULL,

    CNCT_DELIVERY CHAR(1) NOT NULL,
    REDUCED_IND CHAR(1) NOT NULL,
    BOOKED_IN_SGLS INTEGER NOT NULL
    ) END-EXEC.
    ******
    VHUPC

    NAME
    --------------------------
    XDF013A
    XDF013B
    XDF013C
    XDF013D

    TDF013HUPC01
    TDF013HUPC01
    TDF013HUPC01
    TDF013HUPC01

    UNIQUERULE COLCOUNT CLUSTERING CLUSTERED DBID
    ---------- -------- ---------- --------- ------
    D 2 Y Y 372
    D 2 N N 372
    D 4 N N 372
    D 4 N N 372





    NAME
    ----------------------------
    XDFHUPCT1A
    XDFHUPCT2A
    XDFHUPCT3A
    XDFHUPCT4A


    TBNAME
    -----------------------------
    TDF045HUPCT101
    TDF046HUPCT201
    TDF047HUPCT301
    TDF048HUPCT401



    UNIQUERULE COLCOUNT CLUSTERING CLUSTERED DBID OBID ISOBID DBNAME
    ---------- -------- ---------- --------- ------ ------ ------ ----------
    D 2 Y Y 372 174 189 BDF001
    D 1 Y Y 372 210 211 BDF001
    D 1 Y Y 372 222 225 BDF001
    D 1 Y Y 372 227 228 BDF001





    Sathya
    mum

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I would say it is rather slow, due to not having a where clause that limits the data in any way. So, on your main table you are performing a tablespace scan and then all of the left and right joins. This would seem to be a one time run type of thing, but from your question it sounds like you are running this more often than that. What is it you are trying to do and how often?

    Dave

  3. #3
    Join Date
    Feb 2009
    Posts
    2
    All the records fetched from the above select will be inserted into vhupc table, is there any way this querry can be remodified to get the same result...



    below are are two more queries for which a modified query is required.

    UPDATE VSTCOL X
    SET RED_SUFF_IND="Y"
    WHERE "Y" =
    (SELECT MIN(CASE WHEN RED_SUFF = "@"
    THEN "Y"
    ELSE "N"
    END)
    FROM VRDWAY Y
    WHERE X.DEPT_NUM = Y.DEPT_NUM
    AND X.STROKE_NUM = Y.STROKE_NUM
    AND X.COLOUR_CODE = Y.COLOUR_CODE)
    AND "Y" =
    (SELECT MIN(CASE WHEN PRICE = 1
    THEN "Y"
    ELSE "N"
    END)
    FROM VRDWAY Z
    WHERE X.DEPT_NUM = Z.DEPT_NUM
    AND X.STROKE_NUM = Z.STROKE_NUM
    AND X.COLOUR_CODE = Z.COLOUR_CODE)



    EXEC SQL
    DECLARE READNEXT_VHSTCOL CURSOR FOR
    SELECT A.DEPT_NUM,A.STROKE_NUM,A.COLOUR_CODE,UPC_NUM
    FROM VHSTCOL A
    , VRDWAY B
    , VCODES C
    , (SELECT DEPT_NUM, STROKE_NUM, COLOUR_CODE
    , MAX(ADDED_DATE) ADDED_DATE
    FROM VHSTCOL
    GROUP BY DEPT_NUM, STROKE_NUM, COLOUR_CODE) D
    WHERE A.DEPT_NUM = B.DEPT_NUM
    AND A.STROKE_NUM = B.STROKE_NUM
    AND A.COLOUR_CODE = B.COLOUR_CODE
    AND C.FIELD_ID = "US"
    AND A.SUSPENDED_DATE + INTEGER(CODE_VALUE_1) MONTHS
    <= CURRENT DATE
    AND SPM_UNCATLG_DATE IS NULL
    AND A.DEPT_NUM = D.DEPT_NUM
    AND A.STROKE_NUM = D.STROKE_NUM
    AND A.COLOUR_CODE = D.COLOUR_CODE
    AND A.ADDED_DATE = D.ADDED_DATE
    ORDER BY UPC_NUM
    END-EXEC.



    allthe three are embedded in programs
    thanks dave foryour reply....


Posting Permissions

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