Skip to Main Content
  • Questions
  • Procedure is taking too much time to execute

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Ankit.

Asked: October 10, 2018 - 2:52 pm UTC

Last updated: October 13, 2018 - 4:13 am UTC

Version: 12.1.0

Viewed 1000+ times

You Asked

Hi,

We have created procedure with below logic and it's taking too much time to execute,

the main logic we've used as below,

procedure:

create or replace procedure test_parts (
P_QUOTE_NUMBER IN table_c.QUOTE_NUMBER%TYPE,
P_QUOTE_REVISION IN table_b.QUOTE_REVISION%TYPE,
P_RETURN_STATUS OUT NOCOPY VARCHAR2)
IS
l_quote_number table_c.QUOTE_NUMBER%TYPE;
l_quote_revision table_b.QUOTE_REVISION%TYPE;
l_opportunity_id table_c.OPPORTUNITY_ID%TYPE;
l_quote_revision_id table_a.QUOTE_REVISION_ID%TYPE;
l_status VARCHAR2(20);
g_code VARCHAR2(50);
g_msg VARCHAR2(2000);
BEGIN
initialize;
l_quote_number := P_QUOTE_NUMBER;
l_quote_revision := P_QUOTE_REVISION;

BEGIN
SELECT DISTINCT co.opportunity_id,
cqr.revision_id
INTO l_opportunity_id,
l_quote_revision_id
FROM table_b cqr,
table_c cq,
table_d co
WHERE cqr.quote_id = cq.quote_id AND
co.opportunity_id = cq.opportunity_id AND
cqr.quote_revision = l_quote_revision AND
cq.quote_number = l_quote_number;
EXCEPTION
WHEN OTHERS THEN
g_code := SQLCODE;
g_msg := SUBSTR( sqlerrm, 1, 500 );
END;

FOR rec IN (SELECT cqp.hpp_part,
cqp.quote_revision_id,
cqp.prod_type_abc,
cqp.prod_class,
co.opportunity_id,
co.source_products,
cqp.cost_used_for_quote,
cqp.item_tag,
cqp.quote_part_id
FROM table_a cqp,
table_b cqr,
table_c cq,
table_d co
WHERE cqp.quote_revision_id = cqr.revision_id AND
cqr.quote_id = cq.quote_id AND
co.opportunity_id = cq.opportunity_id AND
cqp.queue_id IS NULL AND
cqr.quote_revision = l_quote_revision AND
cq.quote_number = l_quote_number)
LOOP
UPDATE table_a cqp
SET queue_id = (CASE WHEN (cqp.item_tag = g_item_tag) THEN
g_queuetypeelem(8)
WHEN upper(cqp.hpp_part) = g_hpp_part THEN
g_queuetypeelem(1)
WHEN (upper(cqp.prod_type_abc) = g_prodtypeelem(1) OR
upper(cqp.prod_type_abc) = g_prodtypeelem(2)) AND
(cqp.prod_class MEMBER OF g_chemicalarray) THEN
g_queuetypeelem(2)
WHEN (cqp.prod_class MEMBER OF g_lightningarray) THEN
g_queuetypeelem(3)
WHEN (cqp.cost_used_for_quote IS NOT NULL) THEN
g_queuetypeelem(4)
WHEN (cqp.cost_used_for_quote IS NULL AND
upper(rec.source_products) = g_source_products_y) THEN
g_queuetypeelem(5)
WHEN (cqp.cost_used_for_quote IS NULL AND
upper(rec.source_products) = g_source_products_n) THEN
g_queuetypeelem(6)
--WHEN cqp.prod_class MEMBER OF g_kittingarray THEN
--g_queuetypeelem(9)
ELSE g_queuetypeelem(6)
END),
last_updated_date = SYSDATE
WHERE quote_revision_id = rec.quote_revision_id AND
quote_part_id = rec.quote_part_id;
END LOOP;
COMMIT;

IF l_status = 'SUCCESS' THEN
P_RETURN_STATUS := 'SUCCESS';
ELSE
P_RETURN_STATUS := 'FAILURE';
END IF;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
P_RETURN_STATUS := 'FAILURE';
END test_parts;


this for loop is taking long time to update table_a for more than 30K records.

please let us know which other details are required as it's not getting fir in this box.

please suggest is there anything need to change in given for loop?

Thanks


and Connor said...

Doing things in loops is a red flag to me in that I might be able to do it with a single SQL.

So I'd look at something like this

create or replace procedure test_parts (
  p_quote_number     in table_c . quote_number % type ,
  p_quote_revision   in table_b . quote_revision % type ,
  p_return_status    out nocopy varchar2
) is

  l_quote_number        table_c . quote_number % type ;
  l_quote_revision      table_b . quote_revision % type ;
  l_opportunity_id      table_c . opportunity_id % type ;
  l_quote_revision_id   table_a . quote_revision_id % type ;
  l_status              varchar2 ( 20 ) ;
  g_code                varchar2 ( 50 ) ;
  g_msg                 varchar2 ( 2000 ) ;
begin
  initialize ;
  l_quote_number : = p_quote_number ;
  l_quote_revision : = p_quote_revision ;
  
  begin
    select distinct
      co . opportunity_id ,
      cqr . revision_id
    into
      l_opportunity_id ,
      l_quote_revision_id
    from
      table_b cqr ,
      table_c cq ,
      table_d co
    where
      cqr . quote_id = cq . quote_id
      and co . opportunity_id = cq . opportunity_id
      and cqr . quote_revision = l_quote_revision
      and cq . quote_number = l_quote_number ;

    update table_a cqp
    set
      queue_id = (
        case
          when ( cqp . item_tag = g_item_tag ) then g_queuetypeelem ( 8 )
          when upper ( cqp . hpp_part ) = g_hpp_part then g_queuetypeelem ( 1 )
          when (
            upper ( cqp . prod_type_abc ) = g_prodtypeelem ( 1 )
            or upper ( cqp . prod_type_abc ) = g_prodtypeelem ( 2 )
          )
               and ( cqp . prod_class member of g_chemicalarray ) then g_queuetypeelem ( 2 )
          when ( cqp . prod_class member of g_lightningarray ) then g_queuetypeelem ( 3 )
          when ( cqp . cost_used_for_quote is not null ) then g_queuetypeelem ( 4 )
          when (
            cqp . cost_used_for_quote is null
            and upper ( rec . source_products ) = g_source_products_y
          ) then g_queuetypeelem ( 5 )
          when (
            cqp . cost_used_for_quote is null
            and upper ( rec . source_products ) = g_source_products_n
          ) then g_queuetypeelem ( 6 ) 
--WHEN cqp.prod_class MEMBER OF g_kittingarray THEN
--g_queuetypeelem(9)
          else g_queuetypeelem ( 6 )
        end
      ) ,
      last_updated_date = sysdate
    where (quote_revision_id, quote_part_id ) in 
    select
          cqp . quote_revision_id ,
          cqp . hpp_part ,
          cqp . prod_type_abc ,
          cqp . prod_class ,
          co . opportunity_id ,
          co . source_products ,
          cqp . cost_used_for_quote ,
          cqp . item_tag ,
          cqp . quote_part_id
        from
          table_a cqp ,
          table_b cqr ,
          table_c cq ,
          table_d co
        where
          cqp . quote_revision_id = cqr . revision_id
          and cqr . quote_id = cq . quote_id
          and co . opportunity_id = cq . opportunity_id
          and cqp . queue_id is null
          and cqr . quote_revision = l_quote_revision
      and cq . quote_number = l_quote_number
       )
  end loop ;



We can't really comment further because we don't know your data, your data structures etc. But hopefully you can see our intention.

Obviously, add some timings and logging to capture exactly where the time is being lost.

You can see I removed the exception when other stuff because .. well... yuck :-)

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

response

Ankit Bhatt, October 12, 2018 - 4:41 am UTC

Thanks but for one of the condition update statement is failing as "rec . source_products", we are taking value from table_c, so there this update statement is giving error.

please suggest.
Connor McDonald
October 13, 2018 - 4:13 am UTC

If your update is:

update table_a

when the source is:

table_a cqp,
table_b cqr,
table_c cq,
table_d co

Then this suggests either

1) the code is wrong, because you'll be getting multiple rows for each 'table_a' row, OR
2) table_a is the lowest level in the referential integrity hierarchy.

Assuming (2), this probably means you have an opportunity for a key-preserved update, ie

update
  ( 
select
          cqp . quote_revision_id ,
          cqp . hpp_part ,
          cqp . prod_type_abc ,
          cqp . prod_class ,
          co . opportunity_id ,
          co . source_products ,
          cqp . cost_used_for_quote ,
          cqp . item_tag ,
          cqp . quote_part_id
        from
          table_a cqp ,
          table_b cqr ,
          table_c cq ,
          table_d co
        where
          cqp . quote_revision_id = cqr . revision_id
          and cqr . quote_id = cq . quote_id
          and co . opportunity_id = cq . opportunity_id
          and cqp . queue_id is null
          and cqr . quote_revision = l_quote_revision
      and cq . quote_number = l_quote_number
       )
)
set ...



Ankit Bhatt, October 15, 2018 - 4:25 am UTC

Thanks for the suggestion. we have modified the code and worked successfully.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database