tuning the procedure with sql
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: tuning the procedure with sql

  1. #1
    Join Date
    Apr 2003
    Posts
    353

    tuning the procedure with sql

    declare
    CURSOR C3 IS SELECT PS_SHOP_CODE,PS_CONS_DATE,
    TO_NUMBER(TO_CHAR(PS_CONS_DATE,'YYYYMM'))YEAR_MON,PS_PLU_CODE,
    SUM(NVL(DECODE(PS_CONS_TYPE,'R',(-PS_PLU_QTY),PS_PLU_QTY),0)) QTY,
    SUM((NVL(DECODE(PS_CONS_TYPE,'R',(-PS_VALUE),PS_VALUE),0)) -
    (NVL(DECODE(PS_CONS_TYPE,'R',(-PS_DISCOUNT),PS_DISCOUNT),0)) -
    (NVL(DECODE(PS_CONS_TYPE,'R',(-PS_TAX1AMT),PS_TAX1AMT),0)) -
    (NVL(DECODE(PS_CONS_TYPE,'R',(-PS_TAX2AMT),PS_TAX2AMT),0))-
    (NVL(DECODE(PS_CONS_TYPE,'R',(-PS_TAX3AMT),PS_TAX3AMT),0))) SAL_VAL
    FROM TEMP_CONS_DAY GROUP BY PS_SHOP_CODE,PS_CONS_DATE,TO_NUMBER(TO_CHAR(PS_CONS_DATE,'YYYYMM')),PS_PLU_CODE;
    begin
    for i in c1 loop
    UPDATE STOKCUMM SET STCU_SAL_VALUE=NVL(STCU_SAL_VALUE,0) +
    NVL(i.SAL_val,0) WHERE STCU_ITEM_CODE = i.PS_PLU_CODE
    AND YEAR_MON = 20001 AND STCU_BRAN_CODE = 'XXX' ;
    END LOOP;
    END;
    /


    Some of the required details.

    Stokcumm table is having 4millian records of the current year. currently stcu_item_code and year_mon(12 values per year) columns are having indexes.

    The cursor is taking around 8000 records.

    1 Will it give performance gain if I write theabove in a single
    statement?
    2.Am I need to remove the index on year_mon column.

    3.Any other hints on further improving the performance of the above.

    Thanks

  2. #2
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    What is the total no of records in TEMP_CONS_DAY, is there an index on ps_plu_code.

    regards
    anandkl
    anandkl

  3. #3
    Join Date
    Apr 2003
    Posts
    353
    Around 12000.

    There is no index on that field.. Is it required..?

  4. #4
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Its not required
    anandkl

  5. #5
    Join Date
    Apr 2003
    Posts
    353
    My questions is writing that in a single sql statement
    with all the functions will gain performance or not.

  6. #6
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Hi,
    include commit in your loop.
    can u please show us the execution plan of the UPDATE statement

    regards
    anandkl
    anandkl

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Re: tuning the procedure with sql

    Originally posted by engiri
    1 Will it give performance gain if I write theabove in a single
    statement?

    Thanks
    Yes
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Always a single SQL statement works faster than PL/SQL row processing. Go for it.

    Tamil

Posting Permissions

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


Click Here to Expand Forum to Full Width