8 Replies Latest reply on Sep 30, 2013 2:37 PM by Paul Horth

    Need a logic to speed up the processing

    user11273089

      Hi all,

       

      We have two tables Main_data, Stage_data and one view which we access remotely called as View_data.

       

      Data should come from the view to stage and then from stage to main.

       

      for first procedure we are getting difference between View and Main and inserting it into the Stage table.

       

      This procedure is working fine but while inserting the data into the Main table it is taking lots of time

       

      The code is as below. we want to know is there is a another way to write this procedure.

       

      Because we are writing all code in one loop and giving call to the view each time ,which is at the remote location ,it is taking time.

       

      Is there is any way to call the view at once instead of loops.

       

      Table structure for Main ,Stage and view is

       

      Parent_item_code

      Child_item_code

      Root_id

       

      Root_id is not primary key. for 1 roor_id we may have 100 records.

       

       

       

      cursor CUR_root is

               select ROOT_ID STAGE_ROOT  from STAGE_DATA;

      BEGIN

         --Start
         for  C1 in CUR_root
         LOOP
        
         delete from main_data

                      where ROOT_ID=C1.STAGE_ROOT;--- We are deleting all records from main for one root id to load all records again for that roort id from the view.
             
              insert into main_data(PARENT_ITEM_CODE,CHILD_ITEM_CODE,ROOT_ID)       
                      with TBL_VIEW as (select  PARENT_ITEM_CODE,CHILD_ITEM_CODE,ROOT_ID FROM VIEW_DATA
                where ROOT_ID=C1.STAGE_ROOT)
                select PARENT_ITEM_CODE,CHILD_ITEM_CODE,ROOT_ID
                      from tbl_view;

              delete from STAGE_DATA
                      where =ROOT_ID=C1.STAGE_ROOT;

      commit;               
         end loop;                     

       

       

      Please provide me the better solution.Thanks in advance.