Btw, I'm sorry. I have a cut and paste error in method 2.
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)
<iterate property="" open="(" close=")" conjunction="), (">
#prop1.id#,
#prop2.id#
</insert>
-----Original Message-----
From: Poitras Christian [mailto:[EMAIL PROTECTED]
Sent: Monday, December 17, 2007 5:26 PM
To: [email protected]; [EMAIL PROTECTED]
Subject: RE: Best way to insert thousands of records
I use iBATIS in all my calls to database.
I doubt iBATIS would be slower then pure JDBC in this case. But I wonder
if creating a prepared statement of thousands of parameters is better
then just doing batch inserts.
Do you have an opinion about this? Or is there a better solution?
Christian
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Larry Meadors
Sent: Monday, December 17, 2007 5:14 PM
To: [email protected]
Subject: Re: Best way to insert thousands of records
Do we have to use iBATIS...or Java for that matter?
Larry
On Dec 17, 2007 3:00 PM, Poitras Christian
<[EMAIL PROTECTED]> 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
