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
  for rec in (select level L from DUAL connect by level <5000)
    insert into TEMP (a,B,C,D,E) values (REC.L,REC.L,REC.L,REC.L, sysdate);
  end loop;

--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...
Insert into TEMP (A,B,C,D,E) values (1,1,1,1,to_timestamp('09-JUL-12','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','DD-MON-RR HH24.MI.SS.FF'));

--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;
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