Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Posts
    24

    Unanswered: Performance tuning

    Hi , I have written this SP

    CREATE PROCEDURE PRAGS.TESTOT ( )
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    -- Declare variable
    DECLARE ENDTABLE INT DEFAULT 0;
    DECLARE vitem_objid INT DEFAULT 0;
    DECLARE num_rows BIGINT DEFAULT 0;
    DECLARE vs_itemi CHAR(12);
    DECLARE vs_idcng CHAR(12);

    -- Declare cursor
    DECLARE cursor1 CURSOR FOR
    SELECT OBJECT_DATA.ITEM_OBJID, S_ITEMI, S_IDCNG FROM PRAGS.OBJECT_DATA AS OBJECT_DATA INNER JOIN PRAGS.PART_LIST AS PART_LIST
    ON OBJECT_DATA.ITEM_OBJID = PART_LIST.ITEM_OBJID WHERE OBJECT_DATA.S_DC='RD';

    -- Declare handler
    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET ENDTABLE = 1;

    OPEN cursor1;

    SET ENDTABLE = 0;
    FETCH FROM cursor1 INTO vitem_objid, vs_itemi, vs_idcng;
    WHILE ENDTABLE = 0 DO
    SELECT COUNT(*) INTO num_rows FROM PRAGS.AVNTMITEM AVNTMITEM JOIN PRAGS.AVENTACI AVENTACI ON AVNTMITEM.OID=AVENTACI.OID_MITEM JOIN PRAGS.AVNTEC AVNTEC ON AVNTEC.OID=AVENTACI.OID_EC WHERE ITM_ID = vs_itemi AND EC_MC_ID = vs_idcng;

    IF num_rows >= 1 THEN
    UPDATE PRAGS.OBJECT_DATA SET Q_PMSYNC = 'Y' WHERE ITEM_OBJID = vitem_objid AND S_IDCNG = vs_idcng;
    END IF;

    FETCH FROM cursor1 INTO vitem_objid, vs_itemi, vs_idcng;
    END WHILE;
    CLOSE cursor1;
    END P1


    Now the first select query

    "SELECT OBJECT_DATA.ITEM_OBJID, S_ITEMI, S_IDCNG FROM PRAGS.OBJECT_DATA AS OBJECT_DATA INNER JOIN PRAGS.PART_LIST AS PART_LIST
    ON OBJECT_DATA.ITEM_OBJID = PART_LIST.ITEM_OBJID WHERE OBJECT_DATA.S_DC='RD';"

    will return about 2.5 million records.
    How can i improve the performance or is there any way if i can process the records in batches?

    Thanks
    Prashant.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Your best bet for performance is to let DB2 do the work.

    All you need is an update statement that uses the EXISTS clause.

    Andy

  3. #3
    Join Date
    Mar 2009
    Posts
    24
    Quote Originally Posted by ARWinner
    Your best bet for performance is to let DB2 do the work.

    All you need is an update statement that uses the EXISTS clause.

    Andy
    Is thsi what you are talkinh about?
    UPDATE PRAGS.OBJECT_DATA
    SET Q_PMSYNC='Y'
    WHERE EXISTS
    (SELECT *
    FROM (SELECT ITEM_OBJID AS ITEM_OBJID
    , ITM_ID AS PN
    , EC_MC_ID AS EC
    FROM PRAGS.AVNTMITEM AVNTMITEM
    JOIN PRAGS.AVENTACI AVENTACI
    ON AVNTMITEM.OID = AVENTACI.OID_MITEM
    JOIN PRAGS.AVNTEC AVNTEC
    ON AVNTEC.OID = AVENTACI.OID_EC
    JOIN PRAGS.PART_LIST PART_LIST
    ON PART_LIST.S_ITEMI = AVNTMITEM.ITM_ID
    ) AS TEMP_PNEC
    WHERE
    OBJECT_DATA.ITEM_OBJID = TEMP_PNEC.ITEM_OBJID
    AND OBJECT_DATA.S_IDCNG = TEMP_PNEC.EC
    AND OBJECT_DATA.S_DC = 'RD'
    )
    ;

    Which will be better performance wise?
    Is there any way i can still improve the SQL query for performance?

    Thanks.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    From you original post, you want to update every row of PRAGS.OBJECT_DATA (Q_PMSYNC='Y') when there is at least one row that in select * FROM PRAGS.AVNTMITEM AVNTMITEM JOIN PRAGS.AVENTACI AVENTACI ON AVNTMITEM.OID=AVENTACI.OID_MITEM JOIN PRAGS.AVNTEC AVNTEC ON AVNTEC.OID=AVENTACI.OID_EC WHERE ITM_ID = PRAGS.OBJECT_DATA.vs_itemi AND EC_MC_ID = PRAGS.OBJECT_DATA.vs_idcng;

    This would be a simple update statement like this:

    UPDATE PRAGS.OBJECT_DATA as O
    SET Q_PMSYNC='Y'
    WHERE EXISTS
    (select 1 FROM PRAGS.AVNTMITEM AVNTMITEM JOIN PRAGS.AVENTACI AVENTACI ON AVNTMITEM.OID=AVENTACI.OID_MITEM JOIN PRAGS.AVNTEC AVNTEC ON AVNTEC.OID=AVENTACI.OID_EC WHERE ITM_ID = O.vs_itemi AND EC_MC_ID = O.vs_idcng)

    If you have the proper indexes on the tables in the subselect, then this would be the optimal statement.

    Andy

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    WHERE EXISTS
    (select 1 FROM ...
    EXISTS will ignore the selected column list.
    Note: This is documented on "DB2 Universal Database for OS/390 and z/OS SQL Reference Version 7 or later".
    But, it is not ducumented explicitly on DB2 for LUW. I guessed that DB2 for LUW will also ignore the selected list.


    So, I think it will be better to use "*".
    Because, if specified constant, column or expression. I might consider the meaning of the selected list.
    (Other people may have different opinion.)

Posting Permissions

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