Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    41

    Unanswered: Code Optimization.

    we need help to optimize the following update query which is updating 14 fields having null value.the problem is,for each field we have to get a maximum value from the source table and also it should satisfy some conditions.
    //Cursor

    CURSOR UPDATE_CURSOR IS
    SELECT *
    FROM UPDATE_TABLE
    FOR UPDATE OF
    federal
    , state
    , county
    , city
    , other
    , pkg_inst_id
    , type_code
    , certificate
    , status;
    ce_record EXEMPT_UPDATE_CURSOR%ROWTYPE;

    //UPdate

    OPEN UPDATE_CURSOR;
    FETCH UPDATE_CURSOR INTO ce_record;
    WHILE UPDATE_CURSOR %FOUND
    LOOP
    UPDATE UPDATE_TABLE TPC
    SET TPC.federal =
    (SELECT /*+ CHOOSE */
    NVL(MAX(CE.federal),TPC.federal)
    FROM SOURCE CE
    WHERE ((CE.pkg_inst_id = ce_record.pkg_inst_id
    AND CE.type_code = 0)
    OR (CE.pkg_inst_id = 0
    AND CE.type_code = ce_record.type_code)
    OR (CE.pkg_inst_id = 0
    AND CE.type_code = 0))
    AND CE.account_no = ce_record.account_no
    AND (ce_record.exempt_federal != 1
    OR ce_record.exempt_federal IS NULL)
    )
    , TPC.state =
    (SELECT /*+ CHOOSE */
    NVL(MAX(CE.state),TPC.state)
    FROM SOURCE CE
    WHERE ((CE. pkg_inst_id = ce_record.pkg_inst_id
    AND CE. type_code = 0)
    OR (CE. pkg_inst_id = 0
    AND CE. type_code = ce_record.type_code)
    OR (CE. pkg_inst_id = 0
    AND CE. type_code = 0))
    AND CE.account_no = ce_record.account_no
    AND (ce_record.state != 1
    OR ce_record.state IS NULL)
    )
    ....

    WHERE CURRENT OF UPDATE_CURSOR
    ;
    FETCH UPDATE_CURSOR INTO ce_record;

    END LOOP;

    CLOSE UPDATE_CURSOR;

    In the above update query the folwong conditions are common for all the 14 fields.

    ((CE. pkg_inst_id = ce_record.pkg_inst_id
    AND CE. type_code = 0)
    OR (CE. pkg_inst_id = 0
    AND CE. type_code = ce_record.type_code)
    OR (CE. pkg_inst_id = 0
    AND CE. type_code = 0))
    AND CE.account_no = ce_record.account_no

    this update query should executed for each records in update_table and for updation of each field a select query should be executed.Following are the approximate record counts for the each table.

    source table may contain more than 85,000 records.
    update_table may contain more than 11,000 records.

    Note:
    I have created a index for the source table.

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    This may or may not make a difference depending on your execution plan

    1.
    (
    (CE. pkg_inst_id in (ce_record.pkg_inst_id,0) AND CE. type_code = 0)
    OR (CE. pkg_inst_id = 0 AND CE.type_code = ce_record.type_code)
    )
    AND CE.account_no = ce_record.account_no

    2.
    AND (ce_record.state != 1 OR ce_record.state IS NULL)
    can be replaced by
    AND NVL(ce_record.state,0) != 1

    as ORs can lead to performance problems.

    3. An index on source.account_no and also on pkg_inst_id, type_code

    Alan

  3. #3
    Join Date
    Apr 2004
    Posts
    246
    Two suggestions. First, get rid of the loop. From your code, you're simply opening the cursor, updating the row, and loop. This does not require a loop. And, pl/sql looping is slow. Always has been, always will be. The "R" in RDBMS stands for "relational", so try using relational code instead of 3GL code.

    Second, use complex sql to combine the sub-queries for the updates. The code below combines the two sub-queries shown into one. Since they were virtually the same, they are able to be combined, and this will cut down the number of times the same query is executed. Figure out which others can be combined, do it, and test like crazy (especially my code).

    UPDATE UPDATE_TABLE TPC
    SET ( TPC.federal, TPC.state ) =
    (SELECT /*+ CHOOSE */
    NVL(MAX(decode(nvl(ce_record.exempt_federal,-1),1,null,CE.federal)),TPC.federal),
    NVL(MAX(decode(nvl(ce_record.state,-1),1,null,CE.state)),TPC.state)
    FROM SOURCE CE
    WHERE ((CE.pkg_inst_id = ce_record.pkg_inst_id
    AND CE.type_code = 0)
    OR (CE.pkg_inst_id = 0
    AND CE.type_code = ce_record.type_code)
    OR (CE.pkg_inst_id = 0
    AND CE.type_code = 0))
    AND CE.account_no = ce_record.account_no
    )

Posting Permissions

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