Home » SQL & PL/SQL » SQL & PL/SQL » Please help me to tune these procedures (Oracle 11g)
Please help me to tune these procedures [message #582723] Mon, 22 April 2013 04:56 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,

Please help me to tune these two procedures.
I think some unnecessary code is existed in these two procedures.
Please help me to identify those lines.

  PROCEDURE InsertIntoScoreTables(pBUID       IN ORDER_SCORE_REASON.BUID%TYPE,
                                  OrderNum    IN ORDER_SCORE_REASON.ORDER_NUM%TYPE,
                                  ReasonCode  IN MASTER_REASON.REASON_CODE%TYPE,
                                  MatchResult IN ORDER_SCORE_REASON.MATCH_RESULT%TYPE,
                                  ScoreType   IN MASTER_REASON.REASON_TYPE%TYPE,
                                  AddressType IN MASTER_REASON.ADDRESS_TYPE%TYPE) IS

    tCount       NUMBER := 0;
    tScoreID     MASTER_REASON.REASON_ID%TYPE := 0;
    tReasonSeq   MASTER_REASON.REASON_SEQ%TYPE := 0;
    tAddressType MASTER_REASON.ADDRESS_TYPE%TYPE := AddressType;
    tExists NUMBER :=0;

  BEGIN

    if AddressType is NULL then
       tAddressType:='';
    end if;

    IF LENGTH(tAddressType) > 0 THEN
      SELECT COUNT(mstr.reason_code)
        INTO tCount
        FROM MASTER_REASON mstr
       WHERE mstr.REASON_CODE = ReasonCode AND mstr.ENABLED_FLAG = 'Y' AND
             mstr.ADDRESS_TYPE = tAddressType AND
             mstr.REASON_TYPE = ScoreType;
    ELSE
      SELECT COUNT(mstr.reason_code)
        INTO tCount
        FROM MASTER_REASON mstr
       WHERE mstr.REASON_CODE = ReasonCode AND mstr.ENABLED_FLAG = 'Y' AND
             mstr.REASON_TYPE = ScoreType;
    END IF;

    IF tCount > 0 THEN
      IF LENGTH(tAddressType) > 0 THEN
        SELECT mstr.REASON_ID, mstr.REASON_SEQ
          INTO tScoreID, tReasonSeq
          FROM MASTER_REASON mstr
         WHERE mstr.REASON_CODE = ReasonCode AND mstr.ENABLED_FLAG = 'Y' AND
               mstr.ADDRESS_TYPE = tAddressType AND
               mstr.REASON_TYPE = ScoreType AND ROWNUM = 1;
      ELSE
        SELECT mstr.REASON_ID, mstr.REASON_SEQ
          INTO tScoreID, tReasonSeq
          FROM MASTER_REASON mstr
         WHERE mstr.REASON_CODE = ReasonCode AND mstr.ENABLED_FLAG = 'Y' AND
               mstr.REASON_TYPE = ScoreType AND ROWNUM = 1;
      END IF;

      /*INSERT INTO SCORE_REASON_CODES (ORDER_NUM, BUID, ADDRESS_TYPE, REASON_CODE, REASON_SEQ, VENDOR_CODE, MATCH_RESULT)
      VALUES (OrderNum, BUID, tAddressType, ReasonCode, tReasonSeq, VendorCode, MatchResult);*/

      SELECT COUNT(*) INTO tExists FROM ORDER_SCORE_REASON WHERE ORDER_NUM = OrderNum AND BUID = pBUID AND REASON_ID = tScoreID;

      IF tExists > 0 THEN
         DELETE FROM ORDER_SCORE_REASON WHERE ORDER_NUM = OrderNum AND BUID = pBUID AND REASON_ID = tScoreID;
      END IF;

      INSERT INTO ORDER_SCORE_REASON
        (ORDER_NUM,
         BUID,
         REASON_ID,
         REASON_SEQ,
         MATCH_RESULT,
         CREATED_BY,
         CREATED_DATE)
      VALUES
        (OrderNum,
         pBUID,
         tScoreID,
         tReasonSeq,
         MatchResult,
         'ISR',
         SYSDATE);

      --todo: comment below line when done
      --COMMIT;
    END IF;

  END InsertIntoScoreTables;

  PROCEDURE InsertNegScoringtoTable(pBUID         IN ORDER_SCORE_REASON.BUID%TYPE,
                                    OrderNum      IN ORDER_SCORE_REASON.ORDER_NUM%TYPE,
                                    ReasonCode    IN MASTER_REASON.REASON_CODE%TYPE,
                                    MatchResult   IN ORDER_SCORE_REASON.MATCH_RESULT%TYPE,
                                    ScoreType     IN MASTER_REASON.REASON_TYPE%TYPE,
                                    AddressType   IN MASTER_REASON.ADDRESS_TYPE%TYPE,
                                    MatchingBUIDs IN Varchar2) IS

    tCount            NUMBER := 0;
    tScoreReasonCount NUMBER := 0;
    tScoreID          MASTER_REASON.REASON_ID%TYPE := 0;
    tReasonSeq        MASTER_REASON.REASON_SEQ%TYPE := 0;
    tAddressType      MASTER_REASON.ADDRESS_TYPE%TYPE := AddressType;

  BEGIN

    if AddressType is NULL then
       tAddressType:='';
    end if;

    IF LENGTH(tAddressType) > 0 THEN
      SELECT COUNT(mstr.reason_code)
        INTO tCount
        FROM MASTER_REASON mstr
       WHERE mstr.REASON_CODE = ReasonCode AND mstr.ENABLED_FLAG = 'Y' AND
             trim(mstr.ADDRESS_TYPE) = tAddressType AND
             mstr.REASON_TYPE = ScoreType;

    ELSE
      SELECT COUNT(mstr.reason_code)
        INTO tCount
        FROM MASTER_REASON mstr
       WHERE mstr.REASON_CODE = ReasonCode AND mstr.ENABLED_FLAG = 'Y' AND
             mstr.REASON_TYPE = ScoreType;

    END IF;

    IF tCount > 0 THEN
      IF LENGTH(tAddressType) > 0 THEN
        SELECT mstr.REASON_ID, mstr.REASON_SEQ
          INTO tScoreID, tReasonSeq
          FROM MASTER_REASON mstr
         WHERE mstr.REASON_CODE = ReasonCode AND mstr.ENABLED_FLAG = 'Y' AND
               mstr.ADDRESS_TYPE = tAddressType AND
               mstr.REASON_TYPE = ScoreType AND ROWNUM = 1;
      ELSE
        SELECT mstr.REASON_ID, mstr.REASON_SEQ
          INTO tScoreID, tReasonSeq
          FROM MASTER_REASON mstr
         WHERE mstr.REASON_CODE = ReasonCode AND mstr.ENABLED_FLAG = 'Y' AND
               mstr.REASON_TYPE = ScoreType AND ROWNUM = 1;
      END IF;

      /*INSERT INTO SCORE_REASON_CODES (ORDER_NUM, BUID, ADDRESS_TYPE, REASON_CODE, REASON_SEQ, VENDOR_CODE, MATCH_RESULT)
      VALUES (OrderNum, BUID, tAddressType, ReasonCode, tReasonSeq, VendorCode, MatchResult);*/

      If tScoreID is not Null Then
        SELECT COUNT(REASON_ID)
          INTO tScoreReasonCount
          FROM ORDER_SCORE_REASON
         WHERE BUID = pBUID AND ORDER_NUM = OrderNum AND
               REASON_ID = tScoreID;
        If tScoreReasonCount = 0 Then
          INSERT INTO ORDER_SCORE_REASON
            (ORDER_NUM,
             BUID,
             REASON_ID,
             REASON_SEQ,
             MATCH_RESULT,
             CREATED_BY,
             CREATED_DATE,
             MATCH_BUIDS)
          VALUES
            (OrderNum,
             pBUID,
             tScoreID,
             tReasonSeq,
             MatchResult,
             'ISR',
             SYSDATE,
             MatchingBUIDs);
        End If;
      End If;

      --todo: comment below line when done
      --COMMIT;
    END IF;

  END InsertNegScoringtoTable;


Thanks in advance.
Re: Please help me to tune these procedures [message #582725 is a reply to message #582723] Mon, 22 April 2013 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59794
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Before posting a new question you should feedback to the previous ones.
2/ Same answer than in http://www.orafaq.com/forum/mv/msg/187217/582453/102589/#msg_582453 applies
3/ As you don't read and/or accept our answers, why should we still continue to help you (above if you don't feedback)?
4/ Learn to thanks people that help you (even if you don't like their answers).
5/ In the end, tell your boss you are unable to do the tasks he has given to you and he should hire someone more skilled for this.

Regards
Michel
Re: Please help me to tune these procedures [message #582740 is a reply to message #582725] Mon, 22 April 2013 06:26 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
I am extremely sorry.
I forgot to say thanks.
Thanks for your help.
Please help me why I am getting the error.

Thanks in advance.
Re: Please help me to tune these procedures [message #582743 is a reply to message #582740] Mon, 22 April 2013 06:32 Go to previous messageGo to next message
Michel Cadot
Messages: 59794
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What error? There is no error mentioned in your post. You open too many topics at the same time you no more know where you are.
Scan all your previous topics, feedback, thank and put an end comment, so you and we will know where you are.

Regards
Michel
Re: Please help me to tune these procedures [message #582744 is a reply to message #582725] Mon, 22 April 2013 06:33 Go to previous messageGo to next message
cookiemonster
Messages: 11202
Registered: September 2008
Location: Rainy Manchester
Senior Member
As Michel says I've already told you what to do so I'm not going to bother repeating myself. If there's something specific about my suggestion you don't understand you need to point out what.

I will say that
''

Is the same as null as far as oracle is concerned. So this:
if AddressType is NULL then
       tAddressType:='';
    end if;

Does nothing (unless tAddressType is of char type, as opposed to varchar2, in which case that sets the variable to all spaces, looking at your code I doubt that's what you want).
Re: Please help me to tune these procedures [message #583454 is a reply to message #582744] Wed, 01 May 2013 05:01 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Thanks for your reply.
Your suggestion is not helped to me.
Could you please help me to change this logic and remove unnecessary conditions.

Thanks.
Re: Please help me to tune these procedures [message #583463 is a reply to message #582744] Wed, 01 May 2013 05:24 Go to previous messageGo to next message
cookiemonster
Messages: 11202
Registered: September 2008
Location: Rainy Manchester
Senior Member
Again - I've already told you what to do.

cookiemonster wrote on Mon, 22 April 2013 12:33
If there's something specific about my suggestion you don't understand you need to point out what.


I'm not playing guessing games trying to work out what your problem is.

And if you want someone to actually write your code for you, I suggest you hire someone. We help people for free, but if we're actually going to do their job we would probably expect to get paid (not that I'm going to do your job anyway, I've got other things to do).
Re: Please help me to tune these procedures [message #583735 is a reply to message #583463] Fri, 03 May 2013 07:50 Go to previous message
pointers
Messages: 353
Registered: May 2008
Senior Member
@ajaykumarkona:
I think I go by what micheal and cookiemonster suggested as per the guidlines are considered.

In general (Not specific to you),Just dont hesitate to point out what you feel wrong with the suggestion, why it is not helpful, take suggestion till you find the answer. But this continues only when you make others feel comfortable.

in your first proc,
SELECT COUNT(*) INTO tExists FROM ORDER_SCORE_REASON WHERE ORDER_NUM = OrderNum AND BUID = pBUID AND REASON_ID = tScoreID;

      IF tExists > 0 THEN
         DELETE FROM ORDER_SCORE_REASON WHERE ORDER_NUM = OrderNum AND BUID = pBUID AND REASON_ID = tScoreID;
      END IF;

      INSERT INTO ORDER_SCORE_REASON
        (ORDER_NUM,
         BUID,
         REASON_ID,
         REASON_SEQ,
         MATCH_RESULT,
         CREATED_BY,
         CREATED_DATE)
      VALUES
        (OrderNum,
         pBUID,
         tScoreID,
         tReasonSeq,
         MatchResult,
         'ISR',
         SYSDATE);



1. I think you dont need to scan the table ORDER_SCORE_REASON (not sure the size and index of the table) for count.
You can directly execute the following delete statement, if record is found it delets, if not, it will not do anything.

2. I think, you can think of using merge statement as well for the same stuff.

Regards,
Pointers
Previous Topic: Need Help Oon SQL
Next Topic: Select data from 2 tables and insert into another table
Goto Forum:
  


Current Time: Wed Nov 26 16:06:42 CST 2014

Total time taken to generate the page: 0.09372 seconds