Link to home
Start Free TrialLog in
Avatar of gswitz
gswitz

asked on

How to Efficiently insert rows a bunch at a time

I have an application that needs to insert about 1-3 thousand rows into a particular table per transaction. Currently the data is moved over 1 row at a time as an insert update or delete. I was wondering if I could make things better by making a single call to insert a bunch of data by union all the data together in a big single insert. This big insert takes twice the time as inserting a bunch of single insert statements between a begin and end. I was wondering if there isn't a best way to do this...

We are executing from C# and we need a kind of bulk collect into sort of thing.

Any ideas?

Sadly, the example below that takes 17 seconds was the one I was thinking of. I was not only going to use it for inserting but for merging, where some data might be inserted and other data updated.

***
My question is, what is the best way to do large blocks of inserts like the second to last or last example from c# where you are effectively bulk collecting into the table.
***

drop table TEMP;
create table temp nologging as
select 1 a, 1 B, 1 C, 1 D, sysdate E from DUAL;


--1.4 seconds
begin
  for rec in (select level L from DUAL connect by level <5000)
  LOOP
    insert into TEMP (a,B,C,D,E) values (REC.L,REC.L,REC.L,REC.L, sysdate);
  end loop;
  commit;
end;

--0.7 seconds
insert into TEMP select L a,L b,L c,L d, sysdate e from (select level L from DUAL connect by level <5000);

--8.16 seconds (most of the insert statements have been omitted for brevity...
begin
Insert into TEMP (A,B,C,D,E) values (1,1,1,1,to_timestamp('09-JUL-12 15.34.05.000000000','DD-MON-RR HH24.MI.SS.FF'));
...
insert into TEMP (a,B,C,D,E) values (4999,4999,4999,4999,TO_TIMESTAMP('09-JUL-12 15.34.05.000000000','DD-MON-RR HH24.MI.SS.FF'));
end;
/

--query to build the next insert statement
select 'select ' || L || ' a, ' || L  || ' b, ' || L || ' c, ' || L || ' d, sysdate e from dual union all ' a from (select level L from DUAL connect by level <5000);

--17.437 seconds
insert into temp
select 1 a, 1 b, 1 c, 1 d, sysdate e from dual union all
...
select 4998 a, 4998 b, 4998 c, 4998 d, sysdate e from dual union all
select 4999 a, 4999 b, 4999 c, 4999 d, sysdate e from dual;
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gswitz
gswitz

ASKER

Thanks!