pl/sql stored procedure tuning


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: pl/sql stored procedure tuning

  1. #1
    Join Date
    Jun 2006
    Posts
    2

    pl/sql stored procedure tuning

    I have a sql server stored procedure which i migrated to Pl/Sql
    It was taking 16 secs in sql server but in oracle it is taking 33 secs.
    In sql/server nolock hint and making cursors as FAST_FORWARD reduced time a lot. Is there a similar thing available in Oracle, if it is required

    The business logic is like this
    There is a table where each row belongs to a group. Let me call it detail table
    The groups are maintained as rows in a seperate table
    Consider for simplicity that all the records in my detail table belong to the same group and there is only one record in the groups table
    I have to do a row by row processing
    The inputs are as follows:
    1. Criteria. This is a condition (say 2>1) which has to satisfy inorder to process two records
    The steps are:
    1. Take first two records of detail table. Consider the first one as RecA and the second as RecB
    2. Verify if the criteria satisfies for the first two records. If true, we do some processing and get a 3rd record as output. Let me call it op1
    3. Consider op1 as RecA and next record in the table (rec# 3) as RecB and do the same. If the criteria does not satisfy. Mark a pointer on rec# 3.
    4. Consider op1 as RecA and next record (i.e. rec# 4) as RecB and repeat the above steps
    5. Once you reach the end of the table you will have one new record. Say it is op1 in our case. Insert it into the same table.
    6. Now start from the record where the criteria first failed. In our case it is Rec# 3.
    Continue with the above mentioned steps until the end of the table including the newly inserted record

    So this way with in the same group of records i will be iterating multiple times
    Thats the reason whyi have a separate top level loop for the iterating thru groups table and a cursor defined on detail table for each group with in the details table

    The following is test data. This is how it works

    Rule Definition
    Matching Fields - MatchingField
    Records Netted if (Criteria) - RecB.FieldB > RecA.FieldB
    RecA Master if - RecB.FieldA > RecA.FieldA
    Field C (Calculated field) - RecA.FieldC + RecA.UniqueID

    RecordSet
    Status MatchingField FieldA FieldB FieldC UniqueID
    A AA 1 4 A 1
    A AA 2 2 B 2
    A AA 3 3 C 3
    A AA 4 4 D 4
    A AA 5 5 E 5


    First iteration for Unique ID 1
    Status MatchingField FieldA FieldB FieldC UniqueID
    Take first 2 records (1 & 2)
    A AA 1 4 A 1 RecA
    A AA 2 2 B 2 RecB First Non Netted record…so we got to start our 2nd iteration here
    Netting Criteria Fails - so move to the next record

    A AA 1 4 A 1 RecA
    A AA 3 3 C 3 RecB
    Netting Criteria Fails - so move to the next record

    A AA 1 4 A 1 RecA
    A AA 4 4 D 4 RecB
    Netting Criteria Fails - so move to the next record

    A AA 1 4 A 1 RecA Master
    N AA 5 5 E 5 RecB
    A AA 1 4 A1 1 Master

    We have reached the end of the Group…so have to start the second iteration
    A AA 1 4 A1 1 Master
    End of first iteration
    Records for second Iteration
    Status MatchingField FieldA FieldB FieldC UniqueID
    A AA 2 2 B 2
    A AA 3 3 C 3
    A AA 4 4 D 4
    A AA 1 4 A1 1
    --> The output from last iteration has been appended to the end…..

    Second iteration for Unique ID 2
    Status MatchingField FieldA FieldB FieldC UniqueID
    Take first 2 records (2 & 3)
    A AA 2 2 B 2 RecA
    N AA 3 3 C 3 RecB
    Netting Criteria satisfies…so determine the master
    Here RecA remains the Master…perform Netting
    A AA 2 2 B2 2 Master

    A AA 2 2 B2 2 RecA
    A AA 4 4 D 4 RecB
    Netting Criteria satisfies…so determine the master
    here RecA remains the Master…perform Netting
    A AA 2 2 B22 2 Master

    Move to the Next Record
    N AA 2 2 B22 2 RecA
    A AA 1 4 A1 1 RecB Master
    Netting Criteria satisfies…so determine the master
    here RecB becomes the Master…perform Netting
    A AA 1 4 B222 1 Master

    Thanks in advance
    Shiva

  2. #2
    Join Date
    Jun 2006
    Posts
    2

    source code

    Please find the source code of the SP.

    CREATE OR REPLACE Procedure testproc6
    as
    matchTblCnt int; matchTblCntr int;
    master VARCHAR2(6); netting VARCHAR2(6);
    FirstNonNettedRecord int;
    RecordNetted char(1);
    fld0_A INT; fld1_A DATE; fld2_A INT; fld3_A VARCHAR2(1); fld4_A INT; fld5_A INT; ...fld50_A

    fld0_B INT; fld1_B DATE; fld2_B INT; fld3_B VARCHAR2(1); fld4_B INT; fld5_B
    ...fld50_A;
    impIdx int;
    begin
    Declare
    cursor WTCursor is
    select SFASTRtDAT, SFAMATDATE, SFAPRICE, SFAGLC1, SFAGLC2, SFAGLC4, SFABRANCH
    from matchTbl_437271352;
    WTRow WTCursor%ROWTYPE;
    Begin
    Open WTCursor;
    Loop
    Fetch WTCursor into WTRow;
    if WTCursor%Found Then
    Update WorkTable_166403537 Set STBSTATUS = 'N', STBSTATEFORENGINE = 0
    Where NVL(SFASTRtDAT, TO_DATE('01/01/1900', 'dd/mm/yyyy')) = NVL(WTRow.SFASTRtDAT, TO_DATE('01/01/1900', 'dd/mm/yyyy')) and
    NVL(SFAMATDATE, TO_DATE('01/01/1900', 'dd/mm/yyyy')) = NVL(WTRow.SFAMATDATE, TO_DATE('01/01/1900', 'dd/mm/yyyy')) and
    NVL(SFAPRICE, 0) = NVL(WTRow.SFAPRICE, 0) and
    NVL(SFAGLC1, 0) = NVL(WTRow.SFAGLC1, 0) and
    NVL(SFAGLC2, 0) = NVL(WTRow.SFAGLC2, 0) and
    NVL(SFAGLC4, 0) = NVL(WTRow.SFAGLC4, 0) and
    NVL(SFABRANCH, 0) = NVL(WTRow.SFABRANCH, 0);
    Else
    Exit;
    End if;
    End Loop;

    Close WTCursor;
    End;

    matchTblCnt := 0;
    matchTblCntr := 1;
    SELECT count(*) into matchTblCnt FROM matchTbl_437271352;
    if(matchTblCnt = 0) then
    matchTblCntr := 0;
    End if;
    While (matchTblCntr <= matchTblCnt)
    LOOP

    FirstNonNettedRecord := -2;
    While(FirstNonNettedRecord != -1)
    LOOP

    Declare Cursor TTCursor is
    select wt."STBIMPINDEX", wt."STBIMPDATE", wt."STBMAPID", wt."STBSTATUS", wt."STBSTATEFORENGINE", wt."SFABRANCH", wt."SFAGLC1", wt."SFAGLC2", wt."SFAGLC3", wt."SFAGLC4", wt."SFACUSTID", wt."SFASTRTDAT", wt."SFAMATDATE", wt."SFARATE", wt."SFACCY", wt."SFAAMOUNT", wt."SFANOTIONAL", wt."SFAFIELD", wt."SFAFER", wt."SFAORDER", wt."SFAFXOPTIMETH", wt."SFAOPTCCY", wt."SFAOPTVAL", wt."SFAPRICE", wt."SFAINMON", wt."SFAINMPC", wt."SFAEXVAL", wt."SFAWKAMNT", wt."SFAFXBTAMT", wt."SFAFXBTCCY", wt."SFAFXSLDAMT", wt."SFAFXSLCCY", wt."SFATABLE", wt."STBITEM", wt."STBINSTANCE", wt."SFARECORD", wt."SFAFXPRO", wt."SFAMK2MKT", wt."SFABCCY", wt."SFAENTITY", wt."SFAFXOPTMETH", wt."SFAUNDCCY", wt."SFAUNDVAL", wt."SFAHEDGE", wt."SFADELETE", wt."SFARECOUT", wt."SFAREJECT", wt."STBQUERYIND", wt."STBEXECIND", wt."STBNEWIDX", wt."WTRECORDID", TTRECORDID
    From matchTbl_437271352 m INNER JOIN TT_437271352 wt
    ON NVL(m.SFASTRtDAT, TO_DATE('01/01/1900', 'dd/mm/yyyy')) = NVL(wt.SFASTRtDAT, TO_DATE('01/01/1900', 'dd/mm/yyyy')) and
    NVL(m.SFAMATDATE, TO_DATE('01/01/1900', 'dd/mm/yyyy')) = NVL(wt.SFAMATDATE, TO_DATE('01/01/1900', 'dd/mm/yyyy')) and
    NVL(m.SFAPRICE, 0) = NVL(wt.SFAPRICE, 0) and
    NVL(m.SFAGLC1, 0) = NVL(wt.SFAGLC1, 0) and NVL(m.SFAGLC2, 0) = NVL(wt.SFAGLC2, 0) and
    NVL(m.SFAGLC4, 0) = NVL(wt.SFAGLC4, 0) and NVL(m.SFABRANCH, 0) = NVL(wt.SFABRANCH, 0)
    Where m.sl = matchTblCntr and TTRECORDID >= FirstNonNettedRecord and "STBSTATUS" = 'A'
    Order by TTRECORDID;

    Begin
    Open TTCursor;
    RecordNetted := 'F';
    FirstNonNettedRecord := -1;

    fld0_A := NULL; fld1_A := NULL; fld2_A := NULL; fld3_A := NULL; fld4_A := NULL; ...fld50_A := NULL, TTRECORDID_A := NULL;
    Fetch TTCursor into fld0_A, fld1_A, fld2_A, fld3_A, fld4_A, fld5_A, fld6_A, fld7_A, ...,fld50_A TTRECORDID_A;

    if TTCursor%Found Then
    Loop

    fld0_B := NULL; fld1_B := NULL; fld2_B := NULL; fld3_B := NULL; fld4_B := NULL; ...fld50_B := NULL; TTRECORDID_B := NULL;
    Fetch TTCursor into fld0_B, fld1_B, fld2_B, fld3_B, fld4_B, fld5_B, fld6_B, ...fld50_A, TTRECORDID_B;

    if TTCursor%Found Then
    netting := 'false';

    Select CASE WHEN (("RECA"."SFAGLC3"='41' AND "RECB"."SFAGLC3"='43') OR ("RECA"."SFAGLC3"='42' AND "RECB"."SFAGLC3"='44')) THEN CAST ('true' AS VARCHAR(6)) ELSE CAST ('false' AS VARCHAR(6)) END
    Into netting
    from (Select fld0_A "STBIMPINDEX", fld1_A "STBIMPDATE", fld2_A "STBMAPID", fld3_A "STBSTATUS", fld4_A "STBSTATEFORENGINE", fld5_A "SFABRANCH", fld6_A "SFAGLC1", fld7_A "SFAGLC2", fld8_A "SFAGLC3", ..., fld50_A "WTRECORDID", TTRECORDID_A TTRECORDID From Dual) RECA,
    (Select fld0_B "STBIMPINDEX", fld1_B "STBIMPDATE", fld2_B "STBMAPID", fld3_B "STBSTATUS", fld4_B "STBSTATEFORENGINE", fld5_B "SFABRANCH", fld6_B "SFAGLC1", fld7_B "SFAGLC2", fld8_B "SFAGLC3", fld9_B "SFAGLC4", ..., fld50_B "WTRECORDID", TTRECORDID_B TTRECORDID From Dual) RECB;


    if netting = 'true' Then

    master := 'false';
    Select CASE WHEN ("RECA"."SFANOTIONAL">="RECB"."SFANOTIONAL") THEN CAST ('true' AS VARCHAR(6)) ELSE CAST ('false' AS VARCHAR(6)) END
    into master
    from (Select fld0_A "STBIMPINDEX", fld1_A "STBIMPDATE", fld2_A "STBMAPID", fld3_A "STBSTATUS", fld4_A "STBSTATEFORENGINE", ..., fld50_A "WTRECORDID", TTRECORDID_A TTRECORDID From Dual) RECA,
    (Select fld0_B "STBIMPINDEX", fld1_B "STBIMPDATE", fld2_B "STBMAPID", fld3_B "STBSTATUS", fld4_B "STBSTATEFORENGINE", fld5_B "SFABRANCH", ... fld50_B "WTRECORDID", TTRECORDID_B TTRECORDID From Dual) RECB;

    RecordNetted := 'T';

    if master = 'true' Then

    Select "RECA"."SFAOPTVAL"+"RECB"."SFAOPTVAL"
    into fld22_A
    From (Select fld0_A "STBIMPINDEX", fld1_A "STBIMPDATE", fld2_A "STBMAPID", fld3_A "STBSTATUS", fld4_A "STBSTATEFORENGINE", fld5_A "SFABRANCH", fld6_A "SFAGLC1", fld7_A "SFAGLC2", fld8_A "SFAGLC3", ..., fld50_A "WTRECORDID", TTRECORDID_A TTRECORDID From Dual) RECA,
    (Select fld0_B "STBIMPINDEX", fld1_B "STBIMPDATE", fld2_B "STBMAPID", fld3_B "STBSTATUS", fld4_B "STBSTATEFORENGINE", fld5_B "SFABRANCH",
    ..., fld50_B "WTRECORDID", TTRECORDID_B TTRECORDID From Dual) RECB;

    Select "RECA"."SFAMK2MKT"+"RECB"."SFAMK2MKT"
    into fld37_A
    From (Select fld0_A "STBIMPINDEX", fld1_A "STBIMPDATE", fld2_A "STBMAPID", fld3_A "STBSTATUS", fld4_A "STBSTATEFORENGINE", fld5_A "SFABRANCH", ..., fld50_A "WTRECORDID", TTRECORDID_A TTRECORDID From Dual) RECA,
    (Select fld0_B "STBIMPINDEX", fld1_B "STBIMPDATE", fld2_B "STBMAPID", fld3_B "STBSTATUS", fld4_B "STBSTATEFORENGINE", fld5_B "SFABRANCH", ...fld50_B "WTRECORDID", TTRECORDID_B TTRECORDID From Dual) RECB;

    Select "RECA"."SFAINMON"+"RECB"."SFAINMON"
    into fld24_A
    From (Select fld0_A "STBIMPINDEX", fld1_A "STBIMPDATE", fld2_A "STBMAPID", fld3_A "STBSTATUS", fld4_A "STBSTATEFORENGINE", fld5_A "SFABRANCH", ..., fld50_A "WTRECORDID", TTRECORDID_A TTRECORDID From Dual) RECA,
    (Select fld0_B "STBIMPINDEX", fld1_B "STBIMPDATE", fld2_B "STBMAPID", fld3_B "STBSTATUS", fld4_B "STBSTATEFORENGINE", fld5_B "SFABRANCH", ...fld50_B "WTRECORDID", TTRECORDID_B TTRECORDID From Dual) RECB;

    Update TT_437271352 Set STBSTATUS = 'N' where TTRECORDID in (TTRECORDID_A, TTRECORDID_B);

    Else

    Select "RECA"."SFAOPTVAL"+"RECB"."SFAOPTVAL"
    Into fld22_B
    From (Select fld0_A "STBIMPINDEX", fld1_A "STBIMPDATE", fld2_A "STBMAPID", fld3_A "STBSTATUS", fld4_A "STBSTATEFORENGINE", fld5_A "SFABRANCH", ..., fld50_A "WTRECORDID", TTRECORDID_A TTRECORDID From Dual) RECA,
    (Select fld0_B "STBIMPINDEX", fld1_B "STBIMPDATE", fld2_B "STBMAPID", fld3_B "STBSTATUS", fld4_B "STBSTATEFORENGINE", fld5_B "SFABRANCH", ...fld50_B "WTRECORDID", TTRECORDID_B TTRECORDID From Dual) RECB;

    Select "RECA"."SFAMK2MKT"+"RECB"."SFAMK2MKT"
    Into fld37_B
    From (Select fld0_A "STBIMPINDEX", fld1_A "STBIMPDATE", fld2_A "STBMAPID", fld3_A "STBSTATUS", fld4_A "STBSTATEFORENGINE", fld5_A "SFABRANCH", ..., fld50_A "WTRECORDID", TTRECORDID_A TTRECORDID From Dual) RECA,
    (Select fld0_B "STBIMPINDEX", fld1_B "STBIMPDATE", fld2_B "STBMAPID", fld3_B "STBSTATUS", fld4_B "STBSTATEFORENGINE", fld5_B "SFABRANCH", ...fld50_B "WTRECORDID", TTRECORDID_B TTRECORDID From Dual) RECB;

    Select "RECA"."SFAINMON"+"RECB"."SFAINMON"
    Into fld24_B
    From (Select fld0_A "STBIMPINDEX", fld1_A "STBIMPDATE", fld2_A "STBMAPID", fld3_A "STBSTATUS", fld4_A "STBSTATEFORENGINE", fld5_A "SFABRANCH", ..., fld50_A "WTRECORDID", TTRECORDID_A TTRECORDID From Dual) RECA,
    (Select fld0_B "STBIMPINDEX", fld1_B "STBIMPDATE", fld2_B "STBMAPID", fld3_B "STBSTATUS", fld4_B "STBSTATEFORENGINE", fld5_B "SFABRANCH", ...fld50_B "WTRECORDID", TTRECORDID_B TTRECORDID From Dual) RECB;

    fld0_A := fld0_B; fld1_A := fld1_B; fld2_A := fld2_B; fld3_A := fld3_B;
    fld4_A := fld4_B; ...fld50_A := fld50_B;
    Update TT_437271352 Set STBSTATUS = 'N' where TTRECORDID in (TTRECORDID_A, TTRECORDID_B);
    TTRECORDID_A := TTRECORDID_B;

    End if; --if master = 'true'
    Else
    if(FirstNonNettedRecord = -1) Then
    FirstNonNettedRecord := TTRecordID_B;
    End if;
    End if; --if netting = 'true'

    Else
    Exit;
    End if; --if TTCursor%Found
    End Loop;
    End if;
    Close TTCursor;
    End; --begin cursor related begin

    if(RecordNetted = 'F') Then
    Update TT_437271352 set INSERTUPDATE = 2 where TTRECORDID = TTRECORDID_A and INSERTUPDATE != 1;
    Else
    Insert into TT_437271352("STBIMPINDEX", "STBIMPDATE", "STBMAPID", "STBSTATUS", "STBSTATEFORENGINE",..., INSERTUPDATE, TTRECORDID)
    values(fld0_A, fld1_A, fld2_A, fld3_A, fld4_A, fld5_A, fld6_A, fld7_A, fld8_A, ...fld50_A, 1, TT_seq.NextVal);
    End if;

    End LOOP; --While(FirstNonNettedRecord != -1)

    matchTblCntr := matchTblCntr + 1;
    End LOOP; --While (matchTblCntr <= matchTblCnt)

    Select max(STBIMPINDEX) into impIdx from WorkTable_166403537;
    Update TT_437271352 Set STBIMPINDEX = TT_seq2.nextval Where INSERTUPDATE = 1;

    Insert into WorkTable_166403537
    Select "STBIMPINDEX", "STBIMPDATE", "STBMAPID", "STBSTATUS", "STBSTAT...., "STBNEWIDX", TT_seq.NextVal
    from TT_437271352 Where INSERTUPDATE = 1;

    UPDATE WorkTable_166403537 Set STBSTATUS = 'A', STBSTATEFORENGINE = 0
    WHERE Exists (Select 1 from TT_437271352 TT where TT.WTRECORDID = WorkTable_166403537.WTRECORDID And TT.INSERTUPDATE = 2);

    execute immediate 'Drop Table matchTbl_437271352';
    execute immediate 'Drop Table TT_437271352';
    end;
    /

Similar Threads

  1. Stored Procedure SQL Server 2000
    By Michael in forum Database
    Replies: 2
    Last Post: 03-17-2005, 12:07 PM
  2. Replies: 0
    Last Post: 03-29-2002, 12:23 AM
  3. Replies: 0
    Last Post: 02-25-2002, 06:43 PM
  4. Stored procedure
    By Geetha in forum Database
    Replies: 3
    Last Post: 09-12-2001, 05:28 PM
  5. Replies: 4
    Last Post: 04-22-2001, 11:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center