You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Steven Pannell <st...@zooplus.com> on 2005/11/03 07:57:10 UTC
Bulk inserts
Hi,
Is there a faster way of performing bulk inserts?
currently I do something like:
for(int i=1;i < list.size(); i++) {
MyObject object = list.get(i);
sqlMapClient.insert("myInsert", object);
}
This seems to take forever! Is is possible to pass the complete list or
something to save on parsing the statement and getting releasing the
connection for every insert statement??
thx.
Steve.
Re: Bulk inserts
Posted by Larry Meadors <lm...@apache.org>.
I think I'd look for a native database tool for this - most vendors
provide bulk-loading tools.
I think iBATIS is cool, but a golden hammer it isn't. ;-)
Larry
On 11/3/05, Paul Benedict <pa...@yahoo.com> wrote:
> How does it compare to bulk insert? It's faster.
> Making 1000 round trips to the database is slower than
> 1.
>
> Try it out on your database. If it doesn't work, then
> you must do a for loop.
>
> --- Michael Campbell <mi...@gmail.com>
> wrote:
>
> > On 11/3/05, Paul Benedict <pa...@yahoo.com>
> > wrote:
> > > Yes,
> > >
> > > SQL has a notation to INSERT multiple entries at
> > once.
> > > What you want to do is build a statement like the
> > > following:
> > >
> > > INSERT INTO fruit (id, name) VALUES (0, 'Pear'),
> > (1,
> > > 'Apple'), (2, 'Peach')
> >
> >
> > Is that standard ANSI SQL? I know MySql supports
> > that syntax, but
> > wasn't aware that many (any?) others did.
> >
> > --
> > I tend to view "truly flexible" by another term:
> > "Make everything
> > equally hard". -- DHH
> >
>
>
>
>
> __________________________________
> Yahoo! FareChase: Search multiple travel sites in one click.
> http://farechase.yahoo.com
>
Re: Bulk inserts
Posted by Jeff Butler <je...@gmail.com>.
DB2 supports this syntax.
Jeff Butler
On 11/3/05, Michael Campbell <mi...@gmail.com> wrote:
>
> On 11/3/05, Paul Benedict <pa...@yahoo.com> wrote:
> > Yes,
> >
> > SQL has a notation to INSERT multiple entries at once.
> > What you want to do is build a statement like the
> > following:
> >
> > INSERT INTO fruit (id, name) VALUES (0, 'Pear'), (1,
> > 'Apple'), (2, 'Peach')
>
>
> Is that standard ANSI SQL? I know MySql supports that syntax, but
> wasn't aware that many (any?) others did.
>
> --
> I tend to view "truly flexible" by another term: "Make everything
> equally hard". -- DHH
>
Re: Bulk inserts
Posted by Paul Benedict <pa...@yahoo.com>.
How does it compare to bulk insert? It's faster.
Making 1000 round trips to the database is slower than
1.
Try it out on your database. If it doesn't work, then
you must do a for loop.
--- Michael Campbell <mi...@gmail.com>
wrote:
> On 11/3/05, Paul Benedict <pa...@yahoo.com>
> wrote:
> > Yes,
> >
> > SQL has a notation to INSERT multiple entries at
> once.
> > What you want to do is build a statement like the
> > following:
> >
> > INSERT INTO fruit (id, name) VALUES (0, 'Pear'),
> (1,
> > 'Apple'), (2, 'Peach')
>
>
> Is that standard ANSI SQL? I know MySql supports
> that syntax, but
> wasn't aware that many (any?) others did.
>
> --
> I tend to view "truly flexible" by another term:
> "Make everything
> equally hard". -- DHH
>
__________________________________
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com
Re: Bulk inserts
Posted by Michael Campbell <mi...@gmail.com>.
On 11/3/05, Paul Benedict <pa...@yahoo.com> wrote:
> Yes,
>
> SQL has a notation to INSERT multiple entries at once.
> What you want to do is build a statement like the
> following:
>
> INSERT INTO fruit (id, name) VALUES (0, 'Pear'), (1,
> 'Apple'), (2, 'Peach')
Is that standard ANSI SQL? I know MySql supports that syntax, but
wasn't aware that many (any?) others did.
--
I tend to view "truly flexible" by another term: "Make everything
equally hard". -- DHH
RE: Bulk inserts
Posted by Zsolt <zk...@intland.com>.
Paul,
How does it compare to batch processing?
Zsolt
>-----Original Message-----
>From: Paul Benedict [mailto:paul4christ79@yahoo.com]
>Sent: Thursday, November 03, 2005 8:44 AM
>To: user-java@ibatis.apache.org
>Subject: Re: Bulk inserts
>
>Yes,
>
>SQL has a notation to INSERT multiple entries at once.
>What you want to do is build a statement like the
>following:
>
>INSERT INTO fruit (id, name) VALUES (0, 'Pear'), (1,
>'Apple'), (2, 'Peach')
>
>Use the <iterate> tag to build the dynamic list at the
>end. In this example, the GroceryBag object has a
>property of Fruit[] called fruits:
>
><insert id="insertFruit" parameterClass="GroceryBag">
> INSERT INTO fruit
> (id, name)
> VALUES
> <iterate property="fruits" conjunction=",">
> (#fruits[].id#, #fruits[].name#)
> </iterate>
></insert>
>
>
>
>
>__________________________________
>Yahoo! FareChase: Search multiple travel sites in one click.
>http://farechase.yahoo.com
Re: Bulk inserts
Posted by Paul Benedict <pa...@yahoo.com>.
Yes,
SQL has a notation to INSERT multiple entries at once.
What you want to do is build a statement like the
following:
INSERT INTO fruit (id, name) VALUES (0, 'Pear'), (1,
'Apple'), (2, 'Peach')
Use the <iterate> tag to build the dynamic list at the
end. In this example, the GroceryBag object has a
property of Fruit[] called fruits:
<insert id="insertFruit" parameterClass="GroceryBag">
INSERT INTO fruit
(id, name)
VALUES
<iterate property="fruits" conjunction=",">
(#fruits[].id#, #fruits[].name#)
</iterate>
</insert>
__________________________________
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com
Re: Bulk inserts
Posted by Sheshadri Patel <me...@gmail.com>.
On 11/3/05, Steven Pannell <st...@zooplus.com> wrote:
> Hi,
>
> Is there a faster way of performing bulk inserts?
>
> currently I do something like:
>
>
> for(int i=1;i < list.size(); i++) {
>
> MyObject object = list.get(i);
> sqlMapClient.insert("myInsert", object);
>
> }
>
>
> This seems to take forever! Is is possible to pass the complete list or
> something to save on parsing the statement and getting releasing the
> connection for every insert statement??
>
> thx.
> Steve.
Hi,
There's no way to insert List to a Dbase.But you can use executeBatch
provided by iBatis inorder to improve the performance and the network
traffic.
sqlMapClient.startBatch();
for(int i=1;i < list.size(); i++) {
MyObject object = list.get(i);
sqlMapClient.insert("myInsert", object);
}
int no_rows_inserted = sqlMapClient.executeBatch();