using JDBC to bulk insert into oracle

[Jеns Mаrtin Schlаttеr]

I have to insert a few million records into an oracle table using
JDBC.

Here are the methods I know of:

-statement.executeUpdate("insert into ...");

-statement.addBatch("insert into...");
(100 adds then) statement.executeBatch();

-preparedstatement.setInt(1, 20); preparedstatement.setString(2,
"Sales"); preparedstatement.setString(3, "USA");
preparedstatement.executeUpdate();

-preparedstatement.setInt(1, 20); preparedstatement.setString(2,
"Sales"); preparedstatement.setString(3, "USA");
preparedstatement.addBatch();
(100 times, then) preparedstatement.executeBatch();

I guess "preparedstatement.executeBatch();" is the fastest?
I do and executeBatch() every 100 addBatch() or so?

Jens Martin Schlatter

PS: I do not want to use oracle specific code. I need it a quite
generic.

[Thomas Kellerer]

Jеns Mаrtin Schlаttеr wrote on 04.11.2009 22:57:
> I guess "preparedstatement.executeBatch();" is the fastest?
> I do and executeBatch() every 100 addBatch() or so?

Yes.

But make sure that you are using a current driver (10.2.0.4 is the most recent 10.x driver)
There was a *major* performance improvement with the 10.x drivers compared to the 9.x drivers.

My experience is, that numbers above ~200 do not improve performance very much.

But I get about 80%-90% of the SQL*Loader speed when using batched statements with Oracle.

Regards
Thomas

[John B. Matthews]

In article
<82ce6bb0-a88d-45f7...@37g2000yqm.googlegroups.com>,

In addition to batch processing, under some circumstances there may
be an advantage to inserting from more that one thread, each using a
separate connection.

Although Oracle specific, you may also want to look at transportable
tablespaces:

<http://download.oracle.com/docs/cd/E11882_01/server.112/e10595/tspaces013.htm>

Is this a one-time task or a recurring effort?

--
John B. Matthews
trashgod at gmail dot com
<http://sites.google.com/site/drjohnbmatthews>

[Jеns Mаrtin Schlаttеr]

> Although Oracle specific, you may also want to look at transportable
> tablespaces:

Since the data source is not oracle, this will not help.

> Is this a one-time task or a recurring effort?

It is a monthly task.

Thanks,
Martin

[Jеns Mаrtin Schlаttеr]

> But make sure that you are using a current driver (10.2.0.4 is the most recent 10.x driver)
> There was a *major* performance improvement with the 10.x drivers compared to the 9.x drivers.

I use ojdbc5.jar . This does not sound like a 10.2 driver!?

> But I get about 80%-90% of the SQL*Loader speed when using batched statements with Oracle.

This sounds very good, thank you!

Martin

[Thomas Kellerer]

Jеns Mаrtin Schlаttеr, 05.11.2009 15:17:

>> But make sure that you are using a current driver (10.2.0.4 is the most recent 10.x driver)
>> There was a *major* performance improvement with the 10.x drivers compared to the 9.x drivers.
>
> I use ojdbc5.jar . This does not sound like a 10.2 driver!?

The "5" simply indicates that it is intended for Java5. It is not the driver version.
I think it's most probably a 11.x driver :)

To find out the driver version check out the MANIFEST.MF file.

Thomas

[John B. Matthews]

In article
<beea2f5d-54ef-4d9b...@r5g2000yqb.googlegroups.com>,

JÑuns MÑprtin SchlÑpttÑur <KrnBib...@spammotel.com> wrote:

> > Although Oracle specific, you may also want to look at
> > transportable tablespaces:
>
> Since the data source is not oracle, this will not help.

I was thinking not for the source, but for an intermediate tablespace,
which could then be migrated to the destination instance. The result
would be a smaller impact on the destination instance.

> > Is this a one-time task or a recurring effort?
>
> It is a monthly task.

--