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

Avatar of undefined
Last Comment
gswitz

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
gswitz

ASKER
Thanks!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23