Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Validation script (Update statement) took too much time


  • Please log in to reply
1 reply to this topic

#1 Thinker8

Thinker8

  • Members
  • 1 posts
  • OFFLINE
  •  
  • Local time:04:34 AM

Posted 20 June 2015 - 07:40 AM

Hi All,

 

I am newbie to Unix shell script and this forum as well. Please help out to tune my script.

Below mentioned my script will do the required validations and update the corresponding tables. This script took 8 hours to validate almost 200000 records. 

 Please let me know what causing this script to take more time to complete.

DECLARE
   L_reason_chk        VARCHAR2(1);
   L_user_chk          VARCHAR2(1);
   L_item_chk   VARCHAR2(1);
   L_store_chk   VARCHAR2(1);
   L_item_loc_chk      VARCHAR2(1);




CURSOR c_data
    IS 
SELECT FILE_LINE_NO,
       ITEM,
       LOC,
       QTY
  FROM R_ST_DATA;


 CURSOR c_store_chk (L_store store.store%TYPE)
IS
SELECT 'Y'
  FROM store
 WHERE store = L_store;
   
CURSOR c_item_chk (L_item item_master.item%TYPE)
IS
SELECT 'Y'
  FROM item_master
 WHERE item = L_item
   AND tran_level = item_level;
   
CURSOR c_item_loc_chk (L_item item_loc_soh.item%TYPE, L_Loc item_loc_soh.loc%TYPE)
    IS
SELECT 'Y'
  FROM item_loc_soh
 WHERE item = L_item
   AND loc = L_Loc;
   
BEGIN


 -- Remove data from Error table.
 EXECUTE IMMEDIATE 'TRUNCATE TABLE NAT.R_ST_ERR';


  -- Insert error place holders into error reporting table.
 INSERT 
   INTO R_ST_ERR (file_line_no)
 SELECT file_line_no
   FROM R_ST_DATA;


 FOR rt_data_rec IN c_data
 LOOP




 -- Validation for location:


 IF rt_data_rec.loc IS NOT NULL THEN
    
    UPDATE R_ST_ERR
       SET LOC = rt_data_rec.loc,
           LOC_CHK = 'OK'
     WHERE file_line_no = rt_data_rec.file_line_no;


OPEN c_store_chk (rt_data_rec.loc);
     FETCH c_store_chk INTO L_store_chk;
        IF c_store_chk%NOTFOUND THEN
       
       UPDATE R_ST_ERR 
          SET ERROR_IND = 'Y'
        WHERE file_line_no = rt_data_rec.file_line_no;


       UPDATE R_ST_ERR
          SET LOC_CHK = 'Invalid Location'
        WHERE file_line_no = rt_data_rec.file_Line_no;
        END IF;  
    
    CLOSE c_store_chk; 


ELSE


       UPDATE R_ST_ERR 
          SET ERROR_IND = 'Y'
        WHERE file_line_no = rt_data_rec.file_line_no;


       UPDATE R_ST_ERR
          SET LOC_CHK = 'Location is NULL'
        WHERE file_line_no = rt_data_rec.file_line_no;        
    END IF;
  
      
 -- Validate Item information:
  
  IF rt_data_rec.item IS NOT NULL THEN
      ---
     UPDATE R_ST_ERR
        SET item = rt_data_rec.item,
            item_chk = 'OK'
      WHERE file_line_no = rt_data_rec.file_line_no;


    OPEN c_item_chk (rt_data_rec.item);
     FETCH c_item_chk INTO L_item_chk;
        IF c_item_chk%NOTFOUND THEN
       
       UPDATE R_ST_ERR 
          SET ERROR_IND = 'Y'
        WHERE file_line_no = rt_data_rec.file_line_no;


       UPDATE R_ST_ERR
          SET ITEM_CHK = 'Invalid Item'
        WHERE file_line_no = rt_data_rec.file_LIne_no;
        END IF;  
    
    CLOSE c_item_chk; 


ELSE
       UPDATE R_ST_ERR 
          SET ERROR_IND = 'Y'
        WHERE file_line_no = rt_data_rec.file_line_no;


       UPDATE R_ST_ERR
          SET ITEM_CHK = 'Item value is NULL'
        WHERE file_line_no = rt_data_rec.file_line_no;        
    END IF;  


--Item - Location combination validation


     UPDATE R_ST_ERR
        SET item_loc = rt_data_rec.item||' - '||rt_data_rec.loc,
   item_loc_chk = 'OK'
      WHERE file_line_no = rt_data_rec.file_line_no;


    OPEN c_item_loc_chk (rt_data_rec.item,rt_data_rec.loc);
     FETCH c_item_loc_chk INTO L_item_loc_chk;
        IF c_item_loc_chk%NOTFOUND THEN
       
       UPDATE R_ST_ERR 
          SET ERROR_IND = 'Y'
        WHERE file_line_no = rt_data_rec.file_line_no;


       UPDATE R_ST_ERR
          SET ITEM_LOC_CHK = 'Invalid Item-Loc Combination'
        WHERE file_line_no = rt_data_rec.file_Line_no;
        END IF;  
    
    CLOSE c_item_loc_chk; 
          


 -- QTY Validation.
 IF rt_data_rec.QTY IS NOT NULL THEN
    
    UPDATE R_ST_ERR
       SET QTY = rt_data_rec.QTY,
           QTY_CHK = 'OK'
     WHERE file_line_no = rt_data_rec.file_line_no;
     
    
    IF rt_data_rec.QTY = 0 THEN
       UPDATE R_ST_ERR
          SET ERROR_IND = 'Y'
        WHERE file_line_no = rt_data_rec.file_line_no;
        
       UPDATE R_ST_ERR
          SET QTY = rt_data_rec.QTY,
              QTY_CHK = 'Quantity Should Not be Zero'
        WHERE file_line_no = rt_data_rec.file_line_no;
        
    END IF;      
   
     
    IF rt_data_rec.QTY < 0 THEN
       UPDATE R_ST_ERR
          SET ERROR_IND = 'Y'
        WHERE file_line_no = rt_data_rec.file_line_no;
        
       UPDATE R_ST_ERR
          SET QTY = rt_data_rec.QTY,
              QTY_CHK = 'Quantity Should be Positive'
        WHERE file_line_no = rt_data_rec.file_line_no;
    END IF;
     
 ELSE
    UPDATE R_ST_ERR
       SET ERROR_IND = 'Y'
     WHERE file_line_no = rt_data_rec.file_line_no;
     
    UPDATE R_ST_ERR
       SET QTY_CHK = 'Quantity is NULL'
     WHERE file_line_no = rt_data_rec.file_line_no;
 END IF;


  UPDATE R_ST_DATA rst
     SET rst.ERROR_IND = (SELECT ERROR_IND
                            FROM R_ST_ERR
                           WHERE file_line_no = rt_data_rec.file_line_no)
   WHERE file_line_no = rt_data_rec.file_line_no;
   
 END LOOP;


COMMIT;


EXCEPTION
   WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE ('Exception - SQLERRM is'|| to_char(SQLERRM));
   DBMS_OUTPUT.PUT_LINE ('SQL Error Code is '|| to_char(SQLCODE));
   ROLLBACK;
END;

Thanks,


Edited by Thinker8, 20 June 2015 - 07:40 AM.


BC AdBot (Login to Remove)

 


#2 DeimosChaos

DeimosChaos

  • BC Advisor
  • 1,420 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:United States, Delaware
  • Local time:04:34 AM

Posted 22 June 2015 - 01:40 PM

I am not familiar with that code at all... It would probably be better if it was in the Programming section of these forums. I'll see if I can contact an Admin to move it for you.


OS - Ubuntu 14.04/16.04 & Windows 10
Custom Desktop PC / Lenovo Y580 / Sager NP8258 / Dell XPS 13 (9350)
_____________________________________________________
Bachelor of Science in Computing Security from Drexel University
Security +





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users