#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    1
    Rep Power
    0

    Unhappy Delete Records from Oracle 8i goes VERY slow


    OK, first of all, I'm not an experienced Oracle Developer, I write mostly in .NET and my current project involves an Oracle 8i database.

    We have a production database where a lot of production results are stored in 4 tables t_car_hmi, t_comps_hmi, t_bolts_hmi and t_results_hmi.
    Every car can have a lot of comps, every comp can have a lot of bolts, and every bolt can have a lot of results so not very complicated I guess...

    We add results via SQL (builded up based on an XML file) and this goes very smoothly.
    However, If we want to remove entries from this tables, we have some major speed issues...

    I wrote this little procedure based on for loops that goes trough each table


    Code:
    BEGIN 
      FOR SERIAL_ID IN (SELECT SERIAL_ID FROM T_CAR_HMI WHERE T_CAR_HMI.MIX >= 
    '0464730' AND T_CAR_HMI.MIX <= '0464739') LOOP 
        FOR COMPS_ID IN (SELECT COMPS_ID FROM T_COMPS_HMI WHERE 
    T_COMPS_HMI.CAR_ID = SERIAL_ID.SERIAL_ID) LOOP 
          FOR BOLTS_ID IN (SELECT BOLTS_ID FROM T_BOLTS_HMI WHERE 
    T_BOLTS_HMI.COMPS_ID = COMPS_ID.COMPS_ID) LOOP 
              DELETE FROM T_RESULTS_HMI WHERE T_RESULTS_HMI.BOLTS_ID = 
    BOLTS_ID.BOLTS_ID; 
              DELETE FROM T_BOLTS_HMI WHERE T_BOLTS_HMI.BOLTS_ID = 
    BOLTS_ID.BOLTS_ID; 
          END LOOP; 
          COMMIT; 
          DELETE FROM T_COMPS_HMI WHERE T_COMPS_HMI.COMPS_ID = 
    COMPS_ID.COMPS_ID; 
        END LOOP; 
        COMMIT; 
        DELETE FROM T_CAR_HMI WHERE T_CAR_HMI.SERIAL_ID = SERIAL_ID.SERIAL_ID; 
      END LOOP; 
      COMMIT; 
    END;
    This took a minute to complete...

    I tried then the following procedure (after rewriting the data to the DB)

    Code:
    BEGIN
      DELETE FROM t_results_hmi 
      WHERE t_results_hmi.bolts_id in
      (SELECT t_bolts_hmi.bolts_id
      FROM t_bolts_hmi, 
             t_comps_hmi , 
             t_car_hmi 
      WHERE t_bolts_hmi.comps_id = t_comps_hmi.comps_id 
      AND t_comps_hmi.car_id = t_car_hmi.serial_id 
      AND t_car_hmi.mix >= '0464730' 
      AND t_car_hmi.mix <= '0464739');
      COMMIT;
    
      DELETE FROM t_bolts_hmi 
      WHERE t_bolts_hmi.comps_id in
      (SELECT t_comps_hmi.comps_id 
      FROM   t_comps_hmi , 
             t_car_hmi 
      WHERE t_comps_hmi.car_id = t_car_hmi.serial_id 
      AND t_car_hmi.mix >= '0464730' 
      AND t_car_hmi.mix <= '0464739'); 
      COMMIT;
    
      DELETE FROM t_comps_hmi 
      WHERE t_comps_hmi.car_id in
      (SELECT t_car_hmi.serial_id
      FROM t_car_hmi 
      WHERE t_car_hmi.mix >= '0464730' 
      AND t_car_hmi.mix <= '0464739'); 
      COMMIT; 
    
      DELETE FROM t_car_hmi 
      WHERE t_car_hmi.mix >= '0464730' 
      AND t_car_hmi.mix <= '0464739'; 
      COMMIT; 
    END;
    It took over 4 minutes to complete...

    Does anyone have an idea how to increase the speed, because we did this test with 10 records while we have thousands of records in a day...

    Thanks;
    Dries
  2. #2
  3. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    Surely all those nested queries slow it down.
    You should use inner joins instead (not for the last block DELETE FROM t_car_hmi ...).
    Note that if you need to empty the tables the fastest way is to truncate.

IMN logo majestic logo threadwatch logo seochat tools logo