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

    Join Date
    Nov 2003
    Posts
    1
    Rep Power
    0

    Question Insert Proc Optimization


    I'm pretty new to creating Procs in Oracle, I'm coming from SQL Server. I have a procedure that is taking a very long time to run. It loops through about 15 insert statement for the amount of days between two dates.

    My question is,
    would the proc be better optimized using a loop like follows:

    BEGIN
    FOR v_data IN (
    Select statement
    )
    LOOP
    INSERT INTO TABLEA (EMP_GRP_NODE_SK, DESCR, LABEL)
    VALUES (v_data.Col1, v_data.Col2, v_data.Col3);
    END LOOP;
    END;


    or would it be better to just run the insert statement as follows:


    INSERT INTO TableA(Col1, Col2,Col3)
    Select statement;


    Thanks,
    I'd appreciate any help, and or suggestions.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Apr 2002
    Posts
    693
    Rep Power
    26
    as a general rule option 2 will be faster.

IMN logo majestic logo threadwatch logo seochat tools logo