ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Poitras Christian" <Christian.Poit...@ircm.qc.ca>
Subject RE: Best way to insert thousands of records
Date Mon, 17 Dec 2007 22:37:19 GMT
Thanks a lot both of you!
That a possibility I didn't check. I would need to create a temp file
(or some sort of input), but performance is likely to be more
interesting.

Christian


-----Original Message-----
From: Christopher Lamey [mailto:clamey@localmatters.com] 
Sent: Monday, December 17, 2007 5:31 PM
To: user-java@ibatis.apache.org
Subject: Re: Best way to insert thousands of records

Yea, that's immediately what I thought - don't go through Java.  Each
RDBMS has its own bulk import tool.  Oracle has sqlldr, MS SQL Server
has bc.exe, postgres has the copy statement.  They are much much faster
than going the JDBC at all.

On 12/17/07 3:14 PM, "Larry Meadors" <lmeadors@apache.org> wrote:

> Do we have to use iBATIS...or Java for that matter?
> 
> Larry
> 
> 
> On Dec 17, 2007 3:00 PM, Poitras Christian 
> <Christian.Poitras@ircm.qc.ca>
> wrote:
>> 
>> 
>> Hi!
>> 
>> I would like to have your opinion on the fastest way to insert 300k 
>> rows into a database.
>> 
>> Here are 2 ways I tought about.
>> Method 1 :
>> startBatch();
>> for (int i = 0; i < myObjects.size(); i++) {
>>   insert("MyObject.insert", myObjects.get(i)); } executeBatch();
>> 
>> 
>> SqlMap
>> <insert id="insert">
>> INSERT INTO TABLE MyObject(prop1, prop2) VALUES (#prop1.id#, 
>> #prop2.id#) </insert>
>> 
>> 
>> 
>> Method 2 :
>> startBatch();
>> for (int i = 0; i < myObjects.size(); i += 30000) {
>>   insert("MyObject.insert", myObjects.subList(i, Math.min(i + 30000, 
>> myObjects.size()))); } executeBatch();
>> 
>> 
>> 
>> SqlMap
>> <insert id="insert">
>> INSERT INTO TABLE MyObject(prop1, prop2) VALUES (#prop1.id#, 
>> #prop2.id#) </insert>
>> 
>> 
>> Maybe I'm way off... So I would like to know about your experiences.
>> 
>> Thanks
>> Christian


Mime
View raw message