Home » SQL & PL/SQL » SQL & PL/SQL » Please help me to change the logic. (Oracle 11g)
Please help me to change the logic. [message #583087] Thu, 25 April 2013 11:26 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi Experts,

Please help me to change the logic using associative arrays.

CREATE OR REPLACE PROCEDURE GAFT_PROG_DIT.SetScores(pBUID IN SCORE.BUID%TYPE,
                      OrderNum  IN SCORE.ORDER_NUM%TYPE,
                      ScoreType IN VARCHAR2,
                      Score     IN SCORE.VELOCITY_SCORE%TYPE) 
IS
tCount NUMBER := 0;
  BEGIN
    SELECT COUNT(BUID)
      INTO tCount
      FROM SCORE
     WHERE BUID = pBUID AND ORDER_NUM = OrderNum;

    IF tCount = 0 THEN
      INSERT INTO SCORE
        (BUID, ORDER_NUM, RECEIVED_DATE_TIME)
      VALUES
        (pBUID, OrderNum, SYSDATE);
      INSERT INTO SCORE_FILTER_APPLY
        (BUID, ORDER_NUM, RECEIVED_DATE_TIME)
      VALUES
        (pBUID, OrderNum, SYSDATE);
    END IF;

    CASE ScoreType
      WHEN 'V' THEN
        UPDATE SCORE
           SET VELOCITY_SCORE = Score
         WHERE BUID = pBUID AND ORDER_NUM = OrderNum;

        UPDATE SCORE_FILTER_APPLY
           SET VELOCITY_SCORE = Score
         WHERE BUID = pBUID AND ORDER_NUM = OrderNum;

      WHEN 'N' THEN
        UPDATE SCORE
           SET NEGATIVE_SCORE = Score
         WHERE BUID = pBUID AND ORDER_NUM = OrderNum;

        UPDATE SCORE_FILTER_APPLY
           SET NEGATIVE_SCORE = Score
         WHERE BUID = pBUID AND ORDER_NUM = OrderNum;

      WHEN 'T' THEN
        UPDATE SCORE
           SET TREND_SCORE = ROUND(Score)
         WHERE BUID = pBUID AND ORDER_NUM = OrderNum;

        UPDATE SCORE_FILTER_APPLY
           SET TREND_SCORE = ROUND(Score)
         WHERE BUID = pBUID AND ORDER_NUM = OrderNum;

      WHEN 'X' THEN
        UPDATE SCORE
           SET VENDOR_COMBINED_SCORE = Score
         WHERE BUID = pBUID AND ORDER_NUM = OrderNum;

        UPDATE SCORE_FILTER_APPLY
           SET VENDOR_COMBINED_SCORE = Score
         WHERE BUID = pBUID AND ORDER_NUM = OrderNum;

      WHEN 'XB' THEN
        UPDATE SCORE
           SET VENDOR_BT_SCORE = Score
         WHERE BUID = pBUID AND ORDER_NUM = OrderNum;

        UPDATE SCORE_FILTER_APPLY
           SET VENDOR_BT_SCORE = Score
         WHERE BUID = pBUID AND ORDER_NUM = OrderNum;

      WHEN 'XS' THEN
        UPDATE SCORE
           SET VENDOR_ST_SCORE = Score
         WHERE BUID = pBUID AND ORDER_NUM = OrderNum;

        UPDATE SCORE_FILTER_APPLY
           SET VENDOR_ST_SCORE = Score
         WHERE BUID = pBUID AND ORDER_NUM = OrderNum;
    END CASE;

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

  END SetScores;
/


Thanks.
Re: Please help me to change the logic. [message #583088 is a reply to message #583087] Thu, 25 April 2013 11:49 Go to previous messageGo to next message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your problem in doind so? (and what is "so" exactly?)
What did you try so far and where are you stuck?

Regards
Michel
Re: Please help me to change the logic. [message #583109 is a reply to message #583088] Thu, 25 April 2013 23:32 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
The code is being called from from front end .net application.
It's called for each order,suppose there is 200000 orders the code is
calling 200000 times.
So there is a performance issue.

Please help me to change this logic using associative arrays.

Thanks.
Re: Please help me to change the logic. [message #583110 is a reply to message #583109] Thu, 25 April 2013 23:37 Go to previous messageGo to next message
BlackSwan
Messages: 22790
Registered: January 2009
Senior Member
>Please help me to change this logic using associative arrays.

how & why will associative array speed up processing?
Re: Please help me to change the logic. [message #583118 is a reply to message #583109] Fri, 26 April 2013 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ajaykumarkona wrote on Fri, 26 April 2013 06:32
The code is being called from from front end .net application.
It's called for each order,suppose there is 200000 orders the code is
calling 200000 times.
So there is a performance issue.

Please help me to change this logic using associative arrays.

Thanks.


A single or 2 MERGE statement will do it for your 200000 orders.

Regards
Michel

[Updated on: Fri, 26 April 2013 01:46]

Report message to a moderator

Re: Please help me to change the logic. [message #583125 is a reply to message #583087] Fri, 26 April 2013 01:45 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
@ajaykumarkona

Just curious, why do you update two times for each ScoreType ?
For example you wrote in your code
. . .
WHEN 'V' THEN
        UPDATE SCORE
           SET VELOCITY_SCORE = Score
         WHERE BUID = pBUID AND ORDER_NUM = OrderNum;

        UPDATE SCORE_FILTER_APPLY
           SET VELOCITY_SCORE = Score
         WHERE BUID = pBUID AND ORDER_NUM = OrderNum;
. . .

Why do you write two successive of the very same update statement? What's the point/meaning of doing so?


Regards,
Dariyoosh

[Updated on: Fri, 26 April 2013 01:47]

Report message to a moderator

Re: Please help me to change the logic. [message #583126 is a reply to message #583125] Fri, 26 April 2013 01:47 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
Oh sorry I didn't pay attention to the table names Shocked ,
I apologize (please kindly remove my comment)


Regards,
Dariyoosh

[Updated on: Fri, 26 April 2013 01:51]

Report message to a moderator

Re: Please help me to change the logic. [message #583127 is a reply to message #583087] Fri, 26 April 2013 02:09 Go to previous messageGo to next message
John Watson
Messages: 4565
Registered: January 2010
Location: Global Village
Senior Member
Another "just curious": have you considered concurrency? What if one session inserts a new score, and before it commits another session tries to insert or update the same score? SELECT FOR UPDATE might be a quick fix.
Re: Please help me to change the logic. [message #583361 is a reply to message #583087] Tue, 30 April 2013 02:29 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Thanks.
Where to keep SELECT FOR UPDATE in my code.
Please help me.

Thanks in advance.
Re: Please help me to change the logic. [message #583377 is a reply to message #583361] Tue, 30 April 2013 06:19 Go to previous messageGo to next message
sss111ind
Messages: 480
Registered: April 2012
Location: India
Senior Member

CREATE OR REPLACE PROCEDURE gaft_prog_dit.setscores(pbuid IN score.buid%TYPE,
                      ordernum  IN score.order_num%TYPE,
                      scoretype IN VARCHAR2,
                      score     IN score.velocity_score%TYPE) 
IS
tcount NUMBER := 0;
  BEGIN
    SELECT count(buid)
      INTO tcount
      FROM score
     WHERE buid = pbuid AND order_num = ordernum;

    IF tcount = 0 THEN
      INSERT INTO score
        (buid, order_num, received_date_time)
      VALUES
        (pbuid, ordernum, SYSDATE);
      INSERT INTO score_filter_apply
        (buid, order_num, received_date_time)
      VALUES
        (pbuid, ordernum, SYSDATE);
    END IF;

   UPDATE score 
    SET    velocity_score = decode(scoretype, 'V', score), 
           negative_score = decode(scoretype, 'N', score), 
           trend_score = round(decode(scoretype, 'T', score)), 
           vendor_combined_score = decode(scoretype, 'X', score), 
           vendor_bt_score = decode(scoretype, 'XB', score), 
           vendor_st_score = decode(scoretype, 'XS', score) 
    WHERE  buid = pbuid 
           AND order_num = ordernum; 

    UPDATE score_filter_apply 
    SET    velocity_score = decode(scoretype, 'V', score), 
           negative_score = decode(scoretype, 'N', score), 
           trend_score = round(decode(scoretype, 'T', score)), 
           vendor_combined_score = decode(scoretype, 'X', score), 
           vendor_bt_score = decode(scoretype, 'XB', score), 
           vendor_st_score = decode(scoretype, 'XS', score) 
    WHERE  buid = pbuid 
           AND order_num = ordernum; 

   --todo: comment below line when done
   COMMIT;

  END setscores;

[Updated on: Tue, 30 April 2013 06:22]

Report message to a moderator

Re: Please help me to change the logic. [message #583379 is a reply to message #583377] Tue, 30 April 2013 06:24 Go to previous message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
The above couls be written as two merge statements.
Previous Topic: Unique constraint v/s Distinct
Next Topic: Searching the data in the back up tables
Goto Forum:
  


Current Time: Fri Sep 19 11:04:11 CDT 2014

Total time taken to generate the page: 0.05260 seconds