troubleshooting Question

How to Efficiently insert rows a bunch at a time

Avatar of gswitz
gswitz asked on
Oracle Database
2 Comments1 Solution463 ViewsLast Modified:
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
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros