aspose file tools*
The moose likes JDBC and the fly likes Firing SQLs in Batch to enhance performance Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Firing SQLs in Batch to enhance performance" Watch "Firing SQLs in Batch to enhance performance" New topic
Author

Firing SQLs in Batch to enhance performance

Himanshu Khanna
Greenhorn

Joined: Aug 15, 2001
Posts: 29
Dear All ,
I have to insert a million records in Oracle 8i DB thru my java program.Now I am using a SP for insertion . Is there a way to send the records in a batch so as to enhance the performance
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

Others can possibly give better pointers, but I'd probably just reuse the Connection and use a PreparedStatement to allow the database to improve efficiency.
Dave.
Himanshu Khanna
Greenhorn

Joined: Aug 15, 2001
Posts: 29
Thanks for the tip , David ..thats what I have done in the first place just instead of PreparedStatement , I am using CallableStatement as Stored Procedures are being used .
I just wanted to know that is there a way to do a batch INSERT in java - storing SQLs & firing them in one go
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

the CallableStatement interface inherits the addBatch()/executeBatch() methods from PreparedStatement. I trimmed my program down to 10 minutes to run my DML intensive programs that used to take 40 minutes!! Just by using batch statements!
Good luck,
Jamie
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
Is there a reason you can't user sqlloader. IT would be much more efficient.
Dan
Himanshu Khanna
Greenhorn

Joined: Aug 15, 2001
Posts: 29
Jamie , Daniel ,
Thanks for such a quick reply ..I am delighted !!
Jamie could U please tell me a little sample code snippet to use the batch - I need eactly the same amount of drastic change in performance.
We were testing the performance of Java as against C & intention was to insert 50000 records in one go(records were being read from a CSV file )
C did this in 3 mins & my Java code took 18 mins . :-((.
I don't believe that there sud be such a differance & Ur batch sample wud be a GREAT help ..
Thanks a lot once again
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879


if you are using Oracle as your back end, there is a full sample of JDBC Batch Update Performance Extension at http://otn.oracle.com/sample_code/tech/java/sqlj_jdbc/files/advanced/advanced.htm
Jamie
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

There are also performance tips when reading records in from a database. Play with the setFetchSize() and setFetchDirection() methods. Also, using a type 4 driver will give you performance gains.
Jamie
[This message has been edited by Jamie Robertson (edited August 30, 2001).]
Himanshu Khanna
Greenhorn

Joined: Aug 15, 2001
Posts: 29
Thanks Once Again Jamie ! I will try to implement the batch update today & lets see.
One more thing , Could U tell me wether the kind of Oracle driver I use for establishing connection makes a differance on performance i.e. are oci & thin clients the same performance wise or is thin faster ?
I am sorry I started another thread where I have asked the same ques. abt the drivers ..(I posted the ques yesterday)
Himanshu Khanna
Greenhorn

Joined: Aug 15, 2001
Posts: 29
Hi Jamie ,
I tried the Batch insert but it didn't make much of a differance on the speed of record insertion ..maybe there was something wrong in my implementation ..
cstmtTry.setString(1,strEventId);
::::::::::::::::::::::::::
..so..on
..
cstmtTry.setInt(11,nAmnt);
cstmtTry.setString(12,strRecFrm);
cstmtTry.setString(13,strCntctNo);
if (debug) System.out.println("Going for ExecuteUpdate");
cstmtTry.addBatch();
ctr++;
if(ctr == 10)
{
ctr = 0;
insertCount=cstmtTry.executeBatch();
con.commit();
cstmtTry.clearParameters();
}
I am inserting in a batch of 10 ..What I wanted to know was that could the statement " cstmtTry.clearParameters() " be slowing things down for I couldn't figure out why the execution time increased instead of decreasing when I implemented the batch insert ??
One more thing , is there a limit on the no. of batches that I accumulate before executing ?
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Originally posted by Himanshu Khanna:
Hi Jamie ,
I tried the Batch insert but it didn't make much of a differance on the speed of record insertion ..maybe there was something wrong in my implementation ..
cstmtTry.setString(1,strEventId);
::::::::::::::::::::::::::
..so..on
..
cstmtTry.setInt(11,nAmnt);
cstmtTry.setString(12,strRecFrm);
cstmtTry.setString(13,strCntctNo);
if (debug) System.out.println("Going for ExecuteUpdate");
cstmtTry.addBatch();
ctr++;
if(ctr == 10)
{
ctr = 0;
insertCount=cstmtTry.executeBatch();
con.commit();
cstmtTry.clearParameters();
}
I am inserting in a batch of 10 ..What I wanted to know was that could the statement " cstmtTry.clearParameters() " be slowing things down for I couldn't figure out why the execution time increased instead of decreasing when I implemented the batch insert ??
One more thing , is there a limit on the no. of batches that I accumulate before executing ?

I have batched in upwards of 6000 prepared statements. Most of the time saved in a batch update is that it reduces the amount of calls to the db. by only batching 10 statements you lose a lot of the effectiveness of the batch statement. Try batching 1000, then 2000, etc until you find the fastest number per batch. As to a maximum number of batches, I haven't read a maximum value. But at some point, it has to slow down due to memory to store the batches(I would think??). I haven't encountered this problem yet though, so the number must be very high.
Himanshu Khanna
Greenhorn

Joined: Aug 15, 2001
Posts: 29
Thanks Again , Jamie ..
Following Ur advice , I am testing now with batches of 50 ..and the program sure has fastened up !!
Wud keep on increasing the batche size .
In fact after reading Ur response , I straight away set the batch for 1000 :-) but somehow the program slowed down ( I am sure the my program's bug ).
I have seen an increase of 40 % in performance with size of 50 ..
will surely tell u abt the effectiveness increase of the inflated batch sizes..
Thanks Jamie !!
Himanshu Khanna
Greenhorn

Joined: Aug 15, 2001
Posts: 29
Hi jamie ,
Trouble of a new Kind !!!
I am inserting records in the DB using batches of ,say, 1000.
Imagine the parameter to the 500 statement in the Batch has the Primary key value duplicated .This would throw a SQL Exception .
The trouble I am facing is that due to that 500th record , my next 500 records are rolled back too which is not the requirement.
If a particular record is having an already existing Primary key , I need to write it to an error file than proceed to the next record ..
Can you think of a workaround for this specific requirement ?
Thanks for Ur invaluable suggestions Jamie !!
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

I had the same problem when I was working with batch statements. Since I wasn't working with mission critical info, mostly static statistical info, I was able to come up with a work around. I would disable the primary key, insert all the records via batch, then enable the key again with the EXCEPTIONS clause(Oracle DBA tool). I then query the exceptions table and deal with the offending entries.
But, if I had it my way(which I don't), the database relationships would be fixed so it would NOT be possible to have duplicate data. If you are able to fix your database design, I would recommend that first!
anyways, if you use Oracle as a db then I can give you more details, but if you don't??? anyone else have some ideas???
Maybe create your own unique primary key(autonumber or sequence) for the table and make your previous primary key values as foreign keys...then search for duplicate rows using SQL?
Jamie
Himanshu Khanna
Greenhorn

Joined: Aug 15, 2001
Posts: 29
Hi Jamie ,
I have Oracle as a DB so please give Ur valuable tips to achive the desired result !!
I am also dealing with statistical data ..but I can't have my own primary key (auto generated) as the nature of the proj. is such that I need to have one of the supplied data as primary key (infact its a composite primary key ..anyway).I need to keep a track of the offending recs & store them in a error CSV
Thanks again
Himanshu
jasonkosowan
Greenhorn

Joined: Sep 07, 2001
Posts: 25
Up about 5 posts there was a suggestion that JDBC may
not be the best way to insert data into a table. Even
in batch mode, it's really slow compared to a program
called "SQLLoader". What you must do is write your
records to a flatfile (you can make the columns pipe-delimited,
position delimited, it doesn't matter) and then write
a tiny control file that inserts the records into whatever
table you want. It can also generate primary keys on
the fly too. You should really check it out if you want
top-end performance.

Originally posted by Himanshu Khanna:
Hi Jamie ,
I have Oracle as a DB so please give Ur valuable tips to achive the desired result !!
I am also dealing with statistical data ..but I can't have my own primary key (auto generated) as the nature of the proj. is such that I need to have one of the supplied data as primary key (infact its a composite primary key ..anyway).I need to keep a track of the offending recs & store them in a error CSV
Thanks again
Himanshu

Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

I don't think SQL Loader will fix the primary key violation problem though. Did you try using the Exceptions table? Have you found an alternate method of doing this? Did you look at jdbc 3.0(in jdk 1.4) to see if there were any new features that may help you??
just wondering,
Jamie
Himanshu Khanna
Greenhorn

Joined: Aug 15, 2001
Posts: 29
Hi Jamie,
Well , haven't tried the Exceptions table suggestion ..don't know exactly how to go abt doing that ..please give some tips how to go abt doing that ..
Nope haven't explored jdk1.4 ..proj. specification is 1.3 , so that option is ruled out ..
Kind of struck !!
This is a limitation of using batches it seems ..they're appropriate only if U r generating the P Key ..
Thanks again Jamie
Himanshu Khanna
Greenhorn

Joined: Aug 15, 2001
Posts: 29
Hi all & Hello Jamie ,
I think this maybe a bit ofa help on the batch rollback issue(in case of SQLException) -
maybe the class BatchUpdateException needs to be explored .The API documentation does mention about this Exception class though I tried to catch this particular Exception,it wasn't caught ..
[CODE]
for(int i = 0 ; i<5000 ; i++)<br /> {<br /> try<br /> {<br /> // inducing error after 20 elements addition to batch<br /> if(ctr == 20)<br /> {<br /> ps.setInt(1,2000000);<br /> }<br /> else<br /> {<br /> ps.setInt(1,i);<br /> }<br /> ps.setString(2,"Joe Blo");<br /> ctr++;<br /> ps.addBatch();<br /> if(ctr == 250 )<br /> { insertCount=ps.executeBatch();<br /> con.commit(); <br /> ctr = 0;<br /> } <br /> }<br /> catch(BatchUpdateException bue)<br /> {<br /> ctr = 0;<br /> con.commit(); <br /> int [] test = bue.getUpdateCounts() ;<br /> System.out.println("BATCH UPDATE EXCEPTION");<br /> System.out.println("The no of rows inserted are-->" + test.length);
}
catch(SQLException sql)
{
System.out.println("CTR is-->" + ctr);
}
[CODE]
The staetment "BATCH UPDATE EXCEPTION" is not printed instead "CTR is-->" is printed ..can't understand why ?
Alex Ioannou
Greenhorn

Joined: Nov 14, 2001
Posts: 25
I'm a bit confused here.
I want to use prepared statements and batch updates too.
After a simple loop iteration endign with pstmt.addBatch(), do I have to clear the parameters or can I just overwrite them with the set methods again?
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Originally posted by Alex Ioannou:
I'm a bit confused here.
I want to use prepared statements and batch updates too.
After a simple loop iteration endign with pstmt.addBatch(), do I have to clear the parameters or can I just overwrite them with the set methods again?
After 3 years of not clearing parameters, I've not had any problems( using Oracle thin drivers ). The JDBC specs say that a clear parameter call is issued upon a setXXX method anyways. The other answer is that it can't hurt to clear the parameters!
Jamie
Alex Ioannou
Greenhorn

Joined: Nov 14, 2001
Posts: 25
I had a go with the clear parameters like this

Only thing is I kept getting exceptions stating that the parameters were not populated when the batch was executed. Took out the clear method and then everything was fine.
I wonder why that is?
Did I run clearParameters too soon?
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Firing SQLs in Batch to enhance performance