f



using JDBC to bulk insert into oracle

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.



0
11/4/2009 9:57:30 PM
comp.lang.java.databases 3049 articles. 0 followers. samyaksulabh (16) is leader. Post Follow

6 Replies
3576 Views

Similar Articles

[PageSpeed] 15

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


0
Thomas
11/4/2009 10:14:29 PM
In article 
<82ce6bb0-a88d-45f7-8cf0-f3313dcdcdb7@37g2000yqm.googlegroups.com>,
 J�uns M�prtin Schl�ptt�ur <KrnBibJtuEsl@spammotel.com> wrote:

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

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>
0
John
11/5/2009 3:20:18 AM
> 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
0
UTF
11/5/2009 2:16:39 PM
> 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

0
UTF
11/5/2009 2:17:43 PM
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
0
Thomas
11/5/2009 2:44:33 PM
In article 
<beea2f5d-54ef-4d9b-92ce-092cc872f623@r5g2000yqb.googlegroups.com>,
 JÑuns MÑprtin SchlÑpttÑur <KrnBibJtuEsl@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.

-- 
John B. Matthews
trashgod at gmail dot com
<http://sites.google.com/site/drjohnbmatthews>
0
John
11/5/2009 6:18:39 PM
Reply:

Similar Artilces:

Oracle JDBC connect problems-ojdbc6.jar using Native Java commands OK from Matlab,but database toolbox not working -pls help with database toolbox configuration
Hi, I am having some trouble using oracle and jdbc drivers. When I use the native java commands it works from Matlab and I can connect to a database, here is how I do this: javaaddpath('T:\tmp\ojdbc6.jar') aa = oracle.jdbc.pool.OracleDataSource() aa.setDriverType('thin') aa.setNetworkProtocol('tcp') aa.setDatabaseName('TESTDB') aa.setServerName('192.0.0.1') aa.setPortNumber(1521) aa.setUser('testuser') aa.setPassword('testpassword') aa.setServiceName('TESTING') conn = aa.getConnection() stmt = conn.createState...

How to connect Java application with Oracle Database using JDBC?
Hi friends , Myself dilip while learning java, JDBC:ODBC bridge to connect java application with Ms-Access we easily do using DriverManager,Connection,Statement,PreparedStatement,ResultSet, ResultSetMetaData. But when i try to connect java program to Oracle it's gives a number of errors.please ,If anyone has appropriate solution to this provide me. Thanks . Dilip Kumar http://www.intelcs.com/IT-Companies/ public OracleConnection connect() throws Exception {String connectString ="jdbc:oracle:thin:@some.server.name:someportnumber:somedatabasename";OracleDriver driver = new Oracle...

Insert in table on Oracle database using JDBC within RPG?
I have a requirement to load data from DB2/400 into an Oracle database table. I have gotten the sample code (JDBCR4,etc) from Scott Klement's article on accessing the oracle database via JDBC in RPG, however no matter how setup the do loop processing the DB2/400 file I end up with only the last record in the Oracle database table. I looked on the Oracle sight and found an example of intserting multiple rows into a table and based my code to basically perform one insert per record read from the DB2/400 file. Yet when I look at the oracle table I only see 1 row which is the last record in ...

Using Oracle 8.1.7 OCI JDBC Driver WIth Oracle 9i Database
Has anyone had any problems using the 8.1.7 OCI JDBC driver with a 9i database? Oracle states they are compatible at the following URL: http://otn.oracle.com/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm#_1_ "Matt" <mdavey4@csc.com> wrote in message news:f2892d3a.0307241304.6295d05c@posting.google.com... > Has anyone had any problems using the 8.1.7 OCI JDBC driver with a 9i database? > > Oracle states they are compatible at the following URL: > http://otn.oracle.com/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm#_1_ Any reason not to trust that statement? If you do have any reason, why don't you post it rightaway? -- Sybrand Bakker Senior Oracle DBA to reply remove '-verwijderdit' from my e-mail address ...

Bulk Insert jdbc oracle
Hey i am writing a java program which will insert more than 1,00,000 records in to oracle DB. i have read the records from a txt file , i can read it using java and load one by one record into oracle using executeUpdate(), but i want know is there any way by which i can update the records in BULK/BatchUpdate ? pls help me, thanx in advance. rajan On Wed, 04 Jul 2007 03:09:32 -0700, vnethirajan@gmail.com wrote, quoted or indirectly quoted someone who said : >Hey i am writing a java program which will insert more than 1,00,000 >records in to oracle DB. i have read the records from a t...

use java to access oracle database
I am writing a java program to access oracle database. My understanding is that in order to do that I have to: 1) install oracle odbc driver in the windows xp. 2) setup a data source name in windows control panel's data source manager. 3) Download the oracle jdbc driver which is a jar file. 3) Write the java code to connect to the database. My question is: 1) Where can I get oracle odbc driver that can be installed in windows that windows xp will recoginze in ODBC data source administrator? 2) If my database in located on another machine, how to setup the data source in ODBC data so...

Oracle database function using Java
Oracle 10g1, Redhat AS 3.0. I am trying to implement a set of Java methods as database procedures. I tried following class for try. public class ListExperiments { public static int getExperimentCount() { Connection connection = null; int experimentCount = 0; try { connection = new OracleDriver().defaultConnection(); Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery("SELECT count(*) from TT_Experiment"); if (rs.next()) experimentCount = rs...

to use import java.lang.* or import java.lang.Math or none at all?
Hi guys, i knew that by default all java.lang classes will be imported by the compiler during compilation. but, to make it easier for the computer, should i specify which class i really will be using? does this action will boost the performance during compilation and runtime or not a matter at all? the answer to this post will definitely affect my programming style in the future when i'm considering "to import or not to import"... hmm,,, thanks in advance. JPractitioner wrote: > i knew that by default all java.lang classes will be imported by the > compiler during compilation. but, to make it easier for the computer, > should i specify which class i really will be using? does this action > will boost the performance during compilation and runtime or not a > matter at all? Whether and how you import classes has exactly zero effect at runtime. Imports (with or without wildcards) are only a kind of abbreviation provided by the compiler to save us the effort of typing in fully-qualified type names every time. In theory explicit importing should make compilation faster -- by a very tiny amount. I've never heard anyone claim that they've even managed to measure a difference let alone found a case where it made a practical difference. So the question comes down to how to write your code for maximum clarity. One school of thought asserts that you should always import each class explicitly (rather than by a wildcard). There's a fai...

how to insert into oracle database bind vairable in java
Dear ALL I used java to process a text file and insert into oracle database. The insert values is bind constant values but bind vairable. How to bind variable to insert into oracle database in java? can you show me a example, thanks alyda "alydapan" <alydapan@yahoo.com.tw> wrote in message news:478006ec.0504201919.268414fc@posting.google.com... > Dear ALL > > I used java to process a text file and insert into oracle database. > The insert values is bind constant values but bind vairable. > How to bind variable to insert into oracle database in java? > ca...

Insert into ORACLE database using ADODB and VB
Hi, I'm trying to update or insert some records in an ORACLE database using ADODB and VB6. Below is the code I use. When updating, everything works fine. When inserting, the application just hangs. Does anyone have a clue? Any hints would be appreciated! ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''&...

Database insert java/oracle object directly
Hi all, I have the database toolbox, and I connect to an oracle database. When I query/fetch from my table, I get an oracle.sql.STRUCT object returned (this is the correct behaviour - I'm fetching oracle SDO_GEOMETRY). What I want to do, is to somehow *use that struct* (which I think is a java object) in an oracle INSERT. Ie, >> curs = exec(conn,'SELECT mygeom FROM mygeomtable'); >> curs = fetch(curs); >> oraIn = curs.Data{1} oraIn = oracle.sql.STRUCT@1bd96c8 Does anyone know a way to work *with* this oracle.sql.STRUCT@1bd96c8 object, and send it back to the d...

java.lang.AbstractMethodError: oracle.jdbc.driver.OracleResultSetImpl.
Hi All, I am trying to write the jdbc code which is independent to database. I am trying to access the clob datatype for oracle n mysql. But I am getting the following xception javax.servlet.ServletException: oracle.jdbc.driver.OracleResultSetImpl.getClob(Ljava/lang/String;)Ljava/sql/Clob; org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:830) org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:763) org.apache.jsp.htmls.jsps.RuleManagement_jsp._jspService(RuleManagement_jsp.java:461) org.apache.jasper.runtime.Http...

Bug in JDBC Driver with use of Bulk Insert and Money Data Type
In continuation of my previous posts (which might have been lost), I have discovered that when setting IFX_USEPUT=1 , money type fields in inserts which are assigned a NULL value may get a previous non-null value. Here is a Java program that demonstrates the bug: package testcase; import java.math.BigDecimal; import java.sql.Connection; import java.sql.Driver; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; public class BigDecimalBug { public static void main(String[] a...

Oracle 9i, jdbc Linux JAVA insert blob help needed
I've been digging for a while, found several post that supposedly do this but none seem to work. Using Oracle 9i, JAVA 1.4.1, and the latest JDBC from oracle. How do you insert a binary file into a oracle BLOB field? Problems with code I've found: blob = rset.getBlob(); seems getBlob can only be used with java.sql.blob's So if you use a java.sql.blob you can't use getBinaryOutputStream which is only part of the oracle extension. I've even tried this oracle.sql.Blob blob = ((OracleResultSet)rset).getBlob(2); only to get an error that getBlob found java...

connect java application(using jdbc) on windows to connect to Oracle on HP-UX
Hi Group, I would like connect to an Oracle DB running on HP-UX using a java application running on Windows. Could you pls let me know if I need to make any configuration/changes to connect ot Oracle on HP-UX. Do I need any oracle client software to connect to the Oracle DB? If so, will the oracle client on windows be able to communicate with Oracle Server on HP-UX? or should I install oraclec client for HP-UX? Pls let me know. Thank you. regards Anil Just to add to that, we are using jdk1.5 and oracle 9.2.0.6 for HP-UX Thanks. anil wrote: > Just to add to that, we are using jdk1.5...

Best way to check oracle database server / services is alive using Java
Hi, I am looking for the best way to check whether several oracle (or any database) databases is running or not. At the moment I just attempt to connect to the server IP using Socket and conclude the database is not running if connection timed out. Which is a silly way of doing it. Appreciate any help. Best regards. On Apr 3, 9:56 pm, harishas...@gmail.com wrote: > Hi, > > I am looking for the best way to check whether several oracle (or any > database) databases is running or not. > At the moment I just attempt to connect to the server IP using Socket > and conclude...

ora-02055 when insert data from Oracle8170 into remote Oracle 10g database using WAN
information: Local database A:ibm f80 aix 4.3.3 + Oracle81700 local database B: ibm f85 aix 4.3.3 + Oracle81700 remote database C: Redhat EL3 + Oracle10.1.0.2 when i executed the following scripts at a, it raised errors. =3D=3D DELETE sal018tg@idcdrp where fact=3D'3300'; 284503 =E8=A1=8C =E5=B7=B2=E5=88=A0=E9=99=A4 SQL> commit SQL> INSERT INTO sal018tg@idcdrp 2 (cust_no, prod_no, price, start_date, end_date, sis_custno, 3 fact) 4 SELECT cust_no, prod_no, price, start_date, end_date, sis_custno, fact 5 FROM sych_s...

java.sql.SQLException: No suitable driver when connecting to a DB2 database using driver COM.ibm.db2.jdbc.DB2XADataSource
Hi, I want to get connection to a DB2 database using the driver COM.ibm.db2.jdbc.DB2XADataSource. I have also included 'db2java.zip' in the classpath. However I am getting the exception java.sql.SQLException: No suitable driver at java.sql.DriverManager.getConnection(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at Conn.main(Conn.java:44) The code that I am using (with try/catch removed) is as follows: String url = "jdbc:db2:sample"; String driver = "COM.ibm.db2.jdbc.DB2XADataSource"; String dbuser = "db2u...

Do I need oracle database to use oracle developer suite?
I need to use oracle forms and have got oracle developer suite 10g which seems to contain the tools needed for this. But it does not seem to contain the actual oracle database, just development tools... Do I need to install a different product first? Or have I just not found the right option in developer suite? If I need to get the database, what exactly do I need? I believe I can use the developer license for the work I need to do. Thanks for any help, John Burton "John Burton" <john.burton@jbmail.com> wrote in message news:chpd22$hh6@odbk17.prod.google.com... | I need to use oracle forms and have got oracle developer suite 10g | which seems to contain the tools needed for this. | | But it does not seem to contain the actual oracle database, just | development tools... | | Do I need to install a different product first? Or have I just not | found the right option in developer suite? | | If I need to get the database, what exactly do I need? I believe I can | use the developer license for the work I need to do. | Thanks for any help, | John Burton | yes, the database is separate you can download it from OTN should not cause a problem installing it after installing the tools, but perhaps someone else can comment on that fyi: use c.d.o.tools for developer questions use c.d.o.server for database questions ++ mcs John Burton wrote: > I need to use oracle forms and have got oracle developer suite 10g...

java using Oracle Backend
I'm a java web developer and I'm learning 10g in order to consult in Oracle-based shops. After reading much of the Oracle documentation, What I need to learn depends somewhat on how Oracle is being applied. I'd like to hear this group's perceptions of what are typical practices (if they do exist). For example, where is the business logic typically implemented? One way is to implement Oracle object types and use PL/SQL to implement the methods. Another way would be to keep the business classes in Java classes and use SQLJ inside Oracle. How much new work is being done with o...

Using Oracle 8 binaries with an Oracle 9 database
Are there any known issues using Oracle 8 binaries with an Oracle 9 database? don't, bad idea. Jim "steveH" <sjharri@yahoo.com> wrote in message news:324e5dec.0311031243.6b07a00d@posting.google.com... > Are there any known issues using Oracle 8 binaries with an Oracle 9 database? ...

How to define java.lang.Object using java IDL
Hi all, I have a function like: boolean method(java.lang.Object param). How can I define this using java IDL? In another words, I need to pass an instance of java.lang.Object from Client to Server using CORBA. Is this possible? I would be nice simply to typecast the Object at Server-side into whatever needed. How about without typecasts, is this possible in any way, for example using a helper function like: java.lang.Object anyToJavaLangObject(Any value) Code examples ?!? Thanks in advance Juha Rossi ...

inserting data from resultset into oracle database
Hi I am trying to insert the values of a resultset into an oracle database. The problem is that they seem to be inserting in random groups, as oppose to the way they are in the resultset. Basically I have a program getting a certain range of data from a particular table in a SQL server database, copying that data into a ResultSet, and then inserting that data from resultset into an oracle database. The range of data selected from the SQL database, is based on timestamps. So I basically select a range of data between 2 timestamps. This all works fine if i do it for small difference...

Oracle newbie
Appologies if this is a stupid question? Can Internet Directory be used to access an existing Database - i.e access would primarily be via EJB's / JDDBC with LDAP be used as a secondary means of access Not certain what you mean there. Do you mean that login information retained in the Internet Directory store would be retrieved by some application which would then connect to the target database with it? Definitely can be done. That's what it's for, among other things. Oracle has created products that do just this very thing. Some Oracle options depend on it. The Interne...

Web resources about - using JDBC to bulk insert into oracle - comp.lang.java.databases

Insert key - Wikipedia, the free encyclopedia
The Insert key (often abbreviated INS ) is a key commonly found on computer keyboards . It is primarily used to switch between the two text-entering ...

Inserts Wallpapers & Photos on the App Store on iTunes
Get Inserts Wallpapers & Photos on the App Store. See screenshots and ratings, and read customer reviews.

hud-dot-epa south lincoln insert - Flickr - Photo Sharing!
www.sustainablecommunities.gov/pdf/partnership_accomplish...

Lumia 640 XL - How to insert SIM and memory card - YouTube
Learn how to get started with your Lumia 640 XL or Lumia 640 XL Dual SIM. In this video you'll learn how to insert the SIM cards and the memory ...

Top five 'have you seen my (insert body part?)' films
Some flicks are downright prophetic about the need for prosthesis. Seek these films out and ye shall gain from others' anatomic loss. -

Dear [insert name], Qantas takes your complaint very seriously, honest
What's more annoying than being delayed nine hours on a long-haul flight between Sydney and Los Angeles, while stuck in a seat with a broken ...

Skype to insert advertising into calls
Skype to insert advertising into calls

ISIS inserts disturbing images into N.L. vote Twitter chatter
Note: The CBC does not necessarily endorse any of the views posted. By submitting your comments, you acknowledge that CBC has the right to reproduce, ...

Samsung Galaxy Note 5 now ships with warning not to insert S-Pen the wrong way
... YouTube unboxing video reveals that Samsung has begun shipping its Galaxy Note 5 handsets with a warning label, telling users not to insert ...

Updates to Google Docs & Slides let you insert & quickly edit images on iOS
Google is today rolling out updates to its Google Docs and Slides mobile apps bringing the ability to insert images directly from the app on ...

Resources last updated: 2/23/2016 11:30:03 AM