Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2003
    Posts
    19

    Smile Unanswered: Sql update without cursor!

    Hi

    Anybody here to help me to convert this cursor update into single sql update statement without cursor.I am using Oracle 8i.

    PROCEDURE JOB_oRDER_FREE_UPD AS

    BEGIN

    declare cursor curc1 is select doc_year,doc_no,doc_sl_no,sum(ref_db_qty) REF_QTY
    from allocated_stock_detail where doc_year = '20032004'and doc_type = 'JAS'
    GROUP BY DOC_YEAR,DOC_NO,DOC_SL_NO;

    C1 CURC1%ROWTYPE;

    BEGIN

    FOR C1 IN CURC1 LOOP

    UPDATE JOB_ORDER_DETAIL SET JOB_FREE_ITEM = C1.REF_QTY
    WHERE COMP_cODE = 1
    AND JOB_ORDER_YEAR = C1.DOC_YEAR
    AND JOB_oRDER_NO = C1.DOC_NO
    AND JOB_ORDER_SL_NO =C1.DOC_SL_NO;
    COMMIT;

    END LOOP;

    END;

    END;

    Thanks in advance!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    This should do it:
    Code:
    UPDATE JOB_ORDER_DETAIL SET JOB_FREE_ITEM =
    ( select sum(ref_db_qty) REF_QTY
      from allocated_stock_detail
      where doc_year = '20032004'
      and doc_type = 'JAS'
      AND JOB_ORDER_YEAR = DOC_YEAR
      AND JOB_oRDER_NO = DOC_NO
      AND JOB_ORDER_SL_NO = DOC_SL_NO
    )
    WHERE COMP_cODE = 1
    AND (JOB_ORDER_YEAR,JOB_oRDER_NO,JOB_ORDER_SL_NO) IN 
    ( select doc_year,doc_no,doc_sl_no
      from allocated_stock_detail where doc_year = '20032004'
      and doc_type = 'JAS'
    );
    An alternative would be to use your existing cursor solution but with arrays, BULK COLLECT and FORALL.

  3. #3
    Join Date
    Dec 2003
    Posts
    19
    Thanks Tony for your suggestions and query.I thought sql Query without cursor might be faster.But it is still slower like cursor program.I tried your alternative suggestion using BULK COLLECT but i am unable to finish the procedure.I confused whether we have to use array or table for storing and how to refer all the fields in update statement using FORALL.

    As Usual Thanks in Advance!

  4. #4
    Join Date
    Apr 2004
    Posts
    246
    Try:
    UPDATE JOB_ORDER_DETAIL SET JOB_FREE_ITEM =
    ( select sum(ref_db_qty) REF_QTY
    from allocated_stock_detail
    where doc_year = '20032004'
    and doc_type = 'JAS'
    AND JOB_ORDER_YEAR = DOC_YEAR
    AND JOB_oRDER_NO = DOC_NO
    AND JOB_ORDER_SL_NO = DOC_SL_NO
    )
    WHERE COMP_cODE = 1
    AND exists ( select null
    from allocated_stock_detail where doc_year = '20032004'
    AND JOB_ORDER_YEAR = DOC_YEAR
    AND JOB_oRDER_NO = DOC_NO
    AND JOB_ORDER_SL_NO = DOC_SL_NO
    and doc_type = 'JAS'
    );

    also, make sure allocated_stock_detail is indexed on some, if not all, of the columns used in the sub-query

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The BULK COLLECT version will be something like this:
    Code:
    PROCEDURE JOB_oRDER_FREE_UPD AS
    
      cursor curc1 is select doc_year,doc_no,doc_sl_no,sum(ref_db_qty) REF_QTY
      from allocated_stock_detail where doc_year = '20032004'and doc_type = 'JAS'
      GROUP BY DOC_YEAR,DOC_NO,DOC_SL_NO;
    
      TYPE numtab IS TABLE OF NUMBER INDEX BY BINARY INTEGER;
      l_doc_year numtab;
      l_doc_no numtab;
      l_doc_sl_no numtab;
      l_ref_qty numtab;
    BEGIN
    
      OPEN curc1;
      LOOP
        FETCH curc1 BULK COLLECT INTO l_doc_year, l_doc_no, l_doc_sl_no, l_ref_qty LIMIT 100;
      
        FORALL i IN 1..l_ref_qty.COUNT
          UPDATE JOB_ORDER_DETAIL SET JOB_FREE_ITEM = l_ref_qty(i)
          WHERE COMP_cODE = 1
          AND JOB_ORDER_YEAR = l_doc_year(i)
          AND JOB_oRDER_NO = l_doc_no(i)
          AND JOB_ORDER_SL_NO = l_doc_sl_no(i);
    
        EXIT WHEN curc1%NOTFOUND;  
    
      END LOOP;
    
      CLOSE curc1;
    
    END;
    Note: I have used TABLE OF NUMBER for all columns. You may need something different.

  6. #6
    Join Date
    Dec 2003
    Posts
    19
    shoblock:

    Thanks for your reply.sql query without cursor still takes time to execute.

    Tony:

    Thanks a lot for your procedure.now it takes 50 seconds in contrast to
    triple the time before.I wonder how to optimize still.Here are my table details.

    allocated_stock_detail having composite index comprising 4 columns and i used leading two columns in where condition.Number of rows - 2 lakhs+

    JOB_ORDER_DETAIL i am using composite index comprising the same where clause fields. Number of rows - 5 lakhs+

    in JOB_ORDER_DETAIL two more composite index available with same columns combination but with one new field.Is this affect the Updation?

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    So, to be specific, are you saying there is an index on
    JOB_ORDER_DETAIL (JOB_ORDER_YEAR,JOB_ORDER_NO,JOB_ORDER_SL_NO)? That (or any permutation of it) is what the UPDATE wants.

    It sounds like you may have a redundant index though, because if you have:
    INDEX1 ON TABLE1 (A,B,C)
    INDEX2 ON TABLE1 (A,B,C,D)
    then INDEX1 is redundant - it does nothing that INDEX2 doesn't already do.

    A redundant index adds a (smallish) overhead to inserts, but it would not cause any problems with this UPDATE.

    To really see what is happening, run the process with SQL_TRACE set to TRUE and then review the TKPROF output.

  8. #8
    Join Date
    Dec 2003
    Posts
    19
    Thanks tony for your suggestion to use TKPROF. As i am newbie to TKPROF I tried and its hard for me to understand.I pasted the part of the output from TKPROF.Your suggestion will help me a lot.




    BEGIN JOB_oRDER_FREE_UPD('20032004',212,456); END;


    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.00 0.00 0 0 0 0
    Execute 1 0.02 0.02 0 0 0 1
    Fetch 0 0.00 0.00 0 0 0 0
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 2 0.02 0.02 0 0 0 1

    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 41
    ************************************************** ******************************

    select user#
    from
    sys.user$ where name = 'OUTLN'


    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.00 0.00 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 1 0.00 0.00 0 2 0 1
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 3 0.00 0.00 0 2 0 1

    Misses in library cache during parse: 0
    Optimizer goal: CHOOSE
    Parsing user id: SYS (recursive depth: 2)

    Rows Row Source Operation
    ------- ---------------------------------------------------
    1 TABLE ACCESS BY INDEX ROWID USER$
    1 INDEX UNIQUE SCAN (object id 41)

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

    SELECT DOC_YEAR,DOC_NO,DOC_SL_NO,SUM(REF_DB_QTY) REF_QTY
    FROM
    ALLOCATED_STOCK_DETAIL WHERE COMP_CODE = 1 AND DOC_YEAR = '20032004' AND
    DOC_TYPE = 'JAS' GROUP BY DOC_YEAR,DOC_NO,DOC_SL_NO


    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.00 0.00 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 1 1.39 1.40 0 7602 0 164763
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 3 1.39 1.40 0 7602 0 164763

    Misses in library cache during parse: 0
    Optimizer goal: CHOOSE
    Parsing user id: 41 (recursive depth: 1)

    Rows Row Source Operation
    ------- ---------------------------------------------------
    164763 SORT GROUP BY NOSORT
    164763 TABLE ACCESS BY INDEX ROWID ALLOCATED_STOCK_DETAIL
    164764 INDEX RANGE SCAN (object id 24867)

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

    UPDATE JOB_ORDER_DETAIL SET JOB_FREE_ITEM=:b1
    WHERE
    COMP_CODE = 1 AND JOB_ORDER_YEAR = :b2 AND JOB_ORDER_NO = :b3 AND
    JOB_ORDER_SL_NO = :b4


    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.00 0.00 0 0 0 0
    Execute 1 4.55 42.31 0 330216 168146 164075
    Fetch 0 0.00 0.00 0 0 0 0
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 2 4.55 42.31 0 330216 168146 164075

    Misses in library cache during parse: 0
    Optimizer goal: CHOOSE
    Parsing user id: 41 (recursive depth: 1)

    Rows Row Source Operation
    ------- ---------------------------------------------------
    164763 UPDATE JOB_ORDER_DETAIL
    164763 INDEX UNIQUE SCAN (object id 25445)

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

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    That looks pretty good to me. The SELECT gets 164000 rows in 1.4 seconds, and the UPDATE updates 164000 rows in 42 seconds.

    Clearly the UPDATE is the place where any further performance gains could be made. But 164000/42 = 3904 updates/second, which actually sounds quite good.

    One thing puzzles me though: the UPDATE is only executed once according to TKPROF. With the LIMIT 100 I would have expected around 1640 executions. Did you remove the LIMIT or change it to a very large value? If so, try it with LIMIT 100 again - generally it is more efficient to work with 100s of rows at a time in bulk operations. You could experiment with LIMIT values of 200, 500 etc. to see what works best.

  10. #10
    Join Date
    Dec 2003
    Posts
    19
    oops! i removed the LIMIT.But now I tried with LIMIT 200,500 etc.. but not that much of difference(5 to 6 seconds differ).Any OS configuration matters here(my development DB Server-40GB HD and 1GB RAM having 1GB sample data)?

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Maybe someone else can address that question - I can't!

    But as I said before, 42 seconds to update 164000 rows doesn't actually sound unreasonable. The key point with tuning is to know what performance is acceptable, and stop when you get it. You can always find a way to shave another 1% off the time, but that 1% gets smaller each time.

    So: is (roughly) 1 minute definitely unacceptable for this process? If it is only run once a day or less, can't we wait 1 minute?

    Also, often the fastest way to do something is to not do it! Here you are summing values from a detail table and storing the summary value in the master table (right?) Other options would be:
    1) Not store the summary at all - just compute it when required, maybe in a view
    2) Update it via a trigger on the detail table, so that as new detail records are added, the master is updated at the same time (1 master, not 164,000 - so it should be very quick).
    3) Use a materialized view to store the summary. Then you could either query the materialized view, or do option 1 but get the improved query performance from the m.v.

    Just some food for thought!

  12. #12
    Join Date
    Dec 2003
    Posts
    19
    Thanks Tony for your valuable time you spend just for me.I learned lot from you and hats off for your work.

    This procedure calling from vb frontend application,once user click the button he is keep on waiting to finish the process.10 to 15 seconds is ok.Now i am getting 40 sec(apprx).

    Thanks for your food and i am working on it right now!

Posting Permissions

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