tuning the procedures - taking much time
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: tuning the procedures - taking much time

  1. #1
    Join Date
    Jul 2006
    Posts
    96

    tuning the procedures - taking much time

    hi , plz suggest how to tune this procedure.

    i observed that at the statement it hangs. can u tell why?
    should i replace this query?

    ' SELECT COUNT(*) INTO BCOUNT FROM VAM_LEDGER WHERE PARTY=TPARTY AND VCHTYPE IN ('BA','CA') AND VCHNO=c2.vchno'



    CREATE OR REPLACE PROCEDURE VAM_PROC1
    (tPARTY IN varchar,BILL_DT1 DATE, BILL_DATE2 DATE) AS
    begin
    DELETE FROM LED_TEMP WHERE (DRCODE=TPARTY OR CRCODE=TPARTY);
    DELETE FROM LEDGER_TEMP WHERE PARTY=TPARTY;
    COMMIT;
    INSERT INTO LED_TEMP
    SELECT *FROM TRAN WHERE VDATE BETWEEN bill_dt1 AND BILL_DATE2 AND
    (DRCODE=TPARTY OR CRCODE=TPARTY );
    DELETE FROM LED_TEMP WHERE VCHTYPE='JV' AND ADJ_FLAG IS NOT NULL;
    COMMIT;
    DECLARE
    CURSOR C1 IS
    SELECT PARTY,VCHNO,VCHTYPE,BILL_NO,BILL_DT,NARRATION,DRAMT,CRAMT,ADJ_FLAG FROM VAM_LEDGER
    WHERE PARTY=TPARTY AND BILL_DT BETWEEN bill_dt1 AND BILL_DATE2 Order by bill_dt;
    C2 C1%ROWTYPE;
    BAL_AMT1 NUMBER(16,2):=0;
    STAT1 VARCHAR2(2);
    op_bal1 number(10,2);
    pname varchar2(50);
    stat number:=0;
    SN NUMBER:=1;
    BCOUNT NUMBER:=0;
    crdr varchar2(2);
    tdramt number:=0;
    tcramt number:=0;
    tsno number:=0;
    tbalamt number:=0;
    --BILL_DATE1 DATE:='01-APR-06';
    stat2 varchar2(2);
    BEGIN
    select opbal,STAT into op_bal1,CRDR from acc_mast where ac_code=TPARTY;
    IF OP_BAL1>0 THEN
    IF CRDR='CR' then
    op_bal1:=0-op_bal1;
    select name into pname from acc_mast where ac_code=tparty;
    insert into ledger_temp (sno,party,NAME,bill_dt,narration,cramt,BAL_AMT,STAT)
    values (sn,TPARTY,pname,BILL_DT1,'/ OPENING BALANCE -----> ',abs(OP_BAL1),abs(OP_BAL1),'CR');
    elsif crdr='DR' then
    select name into pname from acc_mast where ac_code=tparty;
    insert into ledger_temp (sno,party,NAME,bill_dt,narration,dramt,bal_amt,STAT)
    values (sn,TPARTY,pname,bill_dt1,'/ OPENING BALANCE -----> ',ABS(OP_BAL1),abs(OP_BAL1),'DR');
    end if;
    sn:=2;
    end if;
    select name into pname from acc_mast where ac_code=tparty;
    FOR C2 IN C1 LOOP
    IF C2.VCHTYPE='BD' or c2.vchtype='CR' then
    if SUBSTR(C2.BILL_NO,1,5)='CRBAL' then
    if stat=0 then
    BAL_AMT1:=NVL(BAL_AMT1,0)+NVL(C2.CRAMT,0)-NVL(C2.DRAMT,0)-nvl(OP_BAL1,0);
    stat:=1;
    else
    BAL_AMT1:=NVL(BAL_AMT1,0)+NVL(C2.CRAMT,0)-NVL(C2.DRAMT,0);
    end if;
    IF BAL_AMT1<0 THEN
    STAT1:='DR';
    elsif bal_amt1>0 then
    STAT1:='CR';
    else
    STAT1:=NULL;
    end if;
    insert into ledger_temp (SNO,PARTY,NAME,BILL_NO,BILL_DT,NARRATION,DRAMT,CRAMT,BAL_AMT,STAT)
    VALUES (SN,C2.PARTY,PNAME,C2.BILL_NO,C2.BILL_DT,C2.NARRATION,C2.DRAMT,C2.CRAMT,ABS(BAL_AMT1),STAT1);
    ELSE
    SELECT COUNT(*) INTO BCOUNT FROM VAM_LEDGER WHERE PARTY=TPARTY AND VCHTYPE IN ('BA','CA') AND VCHNO=c2.vchno;



    if bcount=0 then
    if stat=0 then
    BAL_AMT1:=NVL(BAL_AMT1,0)+NVL(C2.CRAMT,0)-NVL(C2.DRAMT,0)-nvl(OP_BAL1,0);
    stat:=1;
    else
    BAL_AMT1:=NVL(BAL_AMT1,0)+NVL(C2.CRAMT,0)-NVL(C2.DRAMT,0);
    end if;
    IF BAL_AMT1<0 THEN
    STAT1:='DR';
    elsif bal_amt1>0 then
    STAT1:='CR';
    else
    STAT1:=NULL;
    end if;
    insert into ledger_temp (SNO,PARTY,NAME,BILL_NO,BILL_DT,NARRATION,DRAMT,CRAMT,BAL_AMT,STAT)
    VALUES (SN,C2.PARTY,PNAME,C2.BILL_NO,C2.BILL_DT,C2.NARRATION,C2.DRAMT,C2.CRAMT,ABS(BAL_AMT1),STAT1);
    elsif bcount>0 then
    null;
    end if;
    END IF;
    else
    if stat=0 then
    BAL_AMT1:=NVL(BAL_AMT1,0)+NVL(C2.CRAMT,0)-NVL(C2.DRAMT,0)-nvl(OP_BAL1,0);
    stat:=1;
    else
    BAL_AMT1:=NVL(BAL_AMT1,0)+NVL(C2.CRAMT,0)-NVL(C2.DRAMT,0);
    end if;
    IF BAL_AMT1<0 THEN
    STAT1:='DR';
    elsif bal_amt1>0 then
    STAT1:='CR';
    else
    STAT1:=NULL;
    end if;
    insert into ledger_temp (SNO,PARTY,NAME,BILL_NO,BILL_DT,NARRATION,DRAMT,CRAMT,BAL_AMT,STAT)
    VALUES (SN,C2.PARTY,PNAME,C2.BILL_NO,C2.BILL_DT,C2.NARRATION,C2.DRAMT,C2.CRAMT,ABS(BAL_AMT1),STAT1);
    end if;
    sn:=sn+1;
    BCOUNT:=0;
    END LOOP;
    COMMIT;
    if BILL_DT1>'01-APR-06' then
    tdramt:=0;
    tcramt:=0;
    STAT2:=NULL;
    DELETE FROM LEDGER_TEMP WHERE PARTY=TPARTY AND BILL_DT SELECT MIN(SNO) INTO TSNO FROM LEDGER_TEMP WHERE PARTY=TPARTY;
    select bal_amt,dramt,cramt,stat into tbalamt,tdramt,tcramt,stat2 from ledger_temp where sno=tsno and party=tparty;
    TSNO:=TSNO-1;
    if nvl(tdramt,0)>0 then
    tbalamt:=tbalamt-tdramt;
    elsif nvl(tcramt,0)>0 then
    tbalamt:=tbalamt+tcramt;
    end if;
    if stat2='DR' then
    insert into ledger_temp (sno,party,NAME,bill_dt,narration,dramt,bal_amt,STAT)
    values (tsno,TPARTY,pname,bill_dt1,'/ OPENING BALANCE -----> ',ABS(TBALAMT),ABS(TBALAMT),STAT2);
    ELSIF stat2='CR' then
    insert into ledger_temp (sno,party,NAME,bill_dt,narration,cramt,bal_amt,STAT)
    values (tsno,TPARTY,pname,bill_dt1,'/ OPENING BALANCE -----> ',ABS(TBALAMT),ABS(TBALAMT),STAT2);
    ELSIF STAT2=NULL THEN
    NULL;
    end if;
    end if;
    END;
    COMMIT;
    END VAM_PROC1;
    /

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Some statistical info would help...
    Number of rows per table?
    Indexes?
    Are stats up-to-date?
    Why do you say it hangs in that specific query?
    Have you looked at explain plans?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jul 2006
    Posts
    96

    procedure tuning - details

    hi, here vam_ledger is a view with total no of rows above one lack.
    the underlying table is led_temp.

    using Toad tool i observed in the option viewing locks.
    the statement

    'select count(*) into btotal from vam_ledger ....................
    ...........

    is shown continuesly for long time after running the procedure.

    if u get any idea now plz give solution.

  4. #4
    Join Date
    Mar 2000
    Location
    Atlanta, GA,USA
    Posts
    155
    Doing row-by-row processing when you can do it in a single SQL is not a good idea to start with. The reason why your SQL shows up as the longest running query is because it executes against a large table as many times as you have rows in the same table (it is inside the for loop created on the same table)! Trying to optimize it is no different from chasing the dog's tail.

    The real problem is lack of SQL (and Pl/SQL) programming knowledge on the developer side. The entire for loop routine can be implemented as a single statement. There are only two things going on inside the loop: 1) if/elsif/else and 2) some math is implemented using about a dozen of unnecessary intermediate variables.

    Here is an example of what I am talking about:
    This:
    IF CRDR='CR' then
    op_bal1:=0-op_bal1;
    select name into pname from acc_mast where ac_code=tparty;
    insert into ledger_temp (sno,party,NAME,bill_dt,narration,cramt,BAL_AMT,STAT)
    values (sn,TPARTY,pname,BILL_DT1,'/ OPENING BALANCE -----> ',abs(OP_BAL1),abs(OP_BAL1),'CR');
    elsif crdr='DR' then
    select name into pname from acc_mast where ac_code=tparty;
    insert into ledger_temp (sno,party,NAME,bill_dt,narration,dramt,bal_amt,STAT)
    values (sn,TPARTY,pname,bill_dt1,'/ OPENING BALANCE -----> ',ABS(OP_BAL1),abs(OP_BAL1),'DR');
    end if;

    ... can be replaced in this:
    insert into ledger_temp (sno,party,NAME,bill_dt,narration,cramt,BAL_AMT,STAT)
    select sn,TPARTY,name,BILL_DT1,'/ OPENING BALANCE -----> ',abs(OP_BAL1),abs(OP_BAL1),CRDR
    from acc_mast where ac_code=tparty;

    ... by only eliminating one unnecessary variable pname, taking crdr variable into the list as oppose to using it in the IF/ELSIF and combining select with insert statements.

    Send the developer to SQL 101 class or hire a better one.

    PS: believe it or not but abs(0-op_bal1) = op_bal1 !!! )

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