CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Java_Alert
Java_Alert

Reputation: 1179

Procedure is consuming lot of time

I have created this procedure.

DECLARE
    cur SYS_REFCURSOR;
    age_compare_group varchar(100);
    state_compare varchar(100);
    gender_compare varchar(100);
    income_compare_groups varchar(100);
    goal varchar(100);
    valuestring varchar(100);
BEGIN    

  OPEN cur FOR
   'select distinct a.age_compare_group, s.state_compare ,  g.gender_compare, i.income_compare_groups,goal_types.goal,goal_types.valuestring
      from age_group a ,
           state s ,
           gender g ,
           income_Group i ,
           (select ''goal'' as GOAL, valuestring
              from appldata
             where dataname like ''GOAL_CAT%''
               and datavalue in (select idcategory
                                   from goal_categories
                                  where typeuser = ''ECU''
                                and id_entity = ''B001''))goal_types';

LOOP                            

      -- process each row one at a time
      FETCH cur into age_compare_group,state_compare, gender_compare,income_compare_groups,goal,valuestring;
      insert into temp_test values ( state_compare ,age_compare_group );

 END LOOP;
    CLOSE cur;
END;

But it is taking lot of time to execute.

I just want to know What so happening and can we optimize it by anyway.

Upvotes: 0

Views: 66

Answers (2)

Basil Farook
Basil Farook

Reputation: 191

Are you trying to insert the query result to another table? If so try to do it as follows,

insert into temp_test select distinct s.state_compare, a.age_compare_group
  from age_group a ,
       state s ,
       gender g ,
       income_Group i ,
       (select 'goal' as GOAL, valuestring
          from appldata
         where dataname like 'GOAL_CAT%'
           and datavalue in (select idcategory
                               from goal_categories
                              where typeuser = 'ECU'
                            and id_entity = 'B001'))goal_types;

Upvotes: 1

Tony Andrews
Tony Andrews

Reputation: 132570

"One row at a time" is always slower than "all rows at once", and using dynamic SQL unnecessarily is slower than using static SQL, so why not try:

begin
      insert into temp_test values ( state_compare ,age_compare_group )
      select distinct s.state_compare, a.age_compare_group
            from age_group a ,
                 state s ,
                 gender g ,
                 income_Group i ,
                 (select 'goal' as GOAL, valuestring
                    from appldata
                   where dataname like 'GOAL_CAT%'
                     and datavalue in (select idcategory
                                         from goal_categories
                                        where typeuser = 'ECU'
                                and id_entity = 'B001'))goal_types;
end;

Upvotes: 2

Related Questions