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 Chris McMahon <co...@hotmail.com> on 2008/03/01 03:10:14 UTC
RE: iBATIS and Batch
This article:
http://java.sun.com/j2se/1.3/docs/guide/jdbc/spec2/jdbc2.1.frame6.html
suggests that you can pass heterogeneous SQL statements to the database using the JDBC batch functionality. However, it was less clear that you could do this with PreparedStatements.
Does iBATIS always use PreparedStatements even if there are no arguments to pass in? In the examples you gave below, you didn't use arguments in your SQL statements.
From: clinton.begin@gmail.com
To: user-java@ibatis.apache.org
Subject: RE: iBATIS and Batch
Date: Fri, 29 Feb 2008 14:31:28 -0700
>> So
you group things together by table?
iBATIS doesn’t know what a table is. It groups them by (Prepared)
SQL Statement.
>> Does
the JDBC driver not let you batch update across tables without changing the
order?
The batching features of JDBC are at the statement level. We
maintain the order of the statements, but otherwise it’s exactly how the JDBC
documentation describes – which is quite vague and more up to the JDBC driver
than anything.
I believe it’s as efficient as it can be, without losing the
order of the statements.
Clinton
From: Chris McMahon
[mailto:cochrane68@hotmail.com]
Sent: February-29-08 2:07 PM
To: user-java@ibatis.apache.org
Subject: RE: iBATIS and Batch
So
you group things together by table?
Does the JDBC driver not let you batch update across tables without changing
the order?
From: clinton.begin@gmail.com
To: user-java@ibatis.apache.org
Subject: RE: iBATIS and Batch
Date: Fri, 29 Feb 2008 11:53:39 -0700
It batches based on the statement. So given this example:
INSERT INTO author (id,username, password, email, bio) VALUES
(1,'jim','********','jim@ibatis.apache.org','');
INSERT INTO author (id,username, password, email, bio) VALUES
(2,'sally','********','sally@ibatis.apache.org',null);
INSERT INTO blog (id,author_id,title) VALUES (1,1,'Jim
Business');
INSERT INTO blog (id,author_id,title) VALUES (2,2,'Bally Slog');
INSERT INTO post (id,blog_id,created_on,subject,body) VALUES
(1,1,'2007-12-05-00.00.00','Corn nuts','I think if I never smelled another corn
nut, it would be too soon...');
INSERT INTO post (id,blog_id,created_on,subject,body) VALUES
(2,1,'2008-01-12-00.00.00','Paul Hogan on Toy Dogs','That''s not a dog.
THAT''s a dog!');
INSERT INTO post (id,blog_id,created_on,subject,body) VALUES
(3,2,'2007-12-05-00.00.00','Monster Trucks','I think monster trucks are
great...');
INSERT INTO post (id,blog_id,created_on,subject,body) VALUES
(4,2,'2008-01-12-00.00.00','Tea Parties','A tea party is no place to hold a
business meeting...');
These would be executed with 3 batches. If you mix up the
order...
INSERT INTO author (id,username, password, email, bio) VALUES
(1,'jim','********','jim@ibatis.apache.org','');
INSERT INTO blog (id,author_id,title) VALUES (1,1,'Jim
Business');
INSERT INTO post (id,blog_id,created_on,subject,body) VALUES
(1,1,'2007-12-05-00.00.00','Corn nuts','I think if I never smelled another corn
nut, it would be too soon...');
INSERT INTO post (id,blog_id,created_on,subject,body) VALUES
(2,1,'2008-01-12-00.00.00','Paul Hogan on Toy Dogs','That''s not a dog.
THAT''s a dog!');
INSERT INTO author (id,username, password, email, bio) VALUES
(2,'sally','********','sally@ibatis.apache.org',null);
INSERT INTO blog (id,author_id,title) VALUES (2,2,'Bally Slog');
INSERT INTO post (id,blog_id,created_on,subject,body) VALUES
(3,2,'2007-12-05-00.00.00','Monster Trucks','I think monster trucks are
great...');
INSERT INTO post (id,blog_id,created_on,subject,body) VALUES
(4,2,'2008-01-12-00.00.00','Tea Parties','A tea party is no place to hold a
business meeting...');
It would execute with 6 batches.
Doing it any other way would cause the statements to execute out
of order, which is a more serious concern and not correctable by the user of
the framework.
Clinton
From: Chris McMahon
[mailto:cochrane68@hotmail.com]
Sent: February-29-08 11:43 AM
To: user-java@ibatis.apache.org
Subject: RE: iBATIS and Batch
We use the batch functionality
frequently. How is iBATIS determining what is homogeneous and what is heterogeneous?
We have a mix of both straight SQL updates and prepared statement updates.
From: clinton.begin@gmail.com
To: user-java@ibatis.apache.org
Subject: RE: iBATIS and Batch
Date: Fri, 29 Feb 2008 08:58:43 -0700
Unfortunately no... that’s possibly something we can consider
for future versions.
The challenge is that some people have the exact opposite
problem. They need the statements to maintain the order of
execution. When initially deciding who to help, I decided that preserving
the order of execution was more important than performance. I also
assumed that if you’re using a mixed order of heterogeneous statements, you
could probably find a way to order them by type and send them through to make
the most of batching. Whereas the opposite wouldn’t be true, as if I
always just reordered the statements for the sake of performance – there would
be no way back. I suppose we could support it as an option, but
before that I have to ask:
Can you reorder your statements? If not, why not?
Clinton
From: chris.mccauley@gsa.gov
[mailto:chris.mccauley@gsa.gov]
Sent: February-29-08 8:47 AM
To: user-java@ibatis.apache.org
Subject: iBATIS and Batch
Recently
we have been implementing batched SQL calls using iBatis through JDBC.
IT appears
that iBatis will reorganize batch statements into homogenous calls and treat
them as separate batches....
The
problem is that is if you do not serially add the homogenous statements, each
'change' from homogeneity results in a separate batch...
Does
this sound correct? Is there something we can do to control the batching
of heterogeneous calls?
Thank
you,
Christopher
Connect
and share in new ways with Windows Live. Get it now!
Helping
your favorite cause is as easy as instant messaging. You IM, we give. Learn more.
_________________________________________________________________
Climb to the top of the charts! Play the word scramble challenge with star power.
http://club.live.com/star_shuffle.aspx?icid=starshuffle_wlmailtextlink_jan
RE: iBATIS and Batch
Posted by Clinton Begin <cl...@gmail.com>.
Yes, iBATIS always uses prepared statements - and I pointed it out for
exactly the reason you suggest: Statements are not as limited with regard
to batching as PreparedStatements.
The JDBC API is kind of gross here. I'm not sure why they couldn't have
created a Batch class that you could add statements to and then execute. It
would have been a lot more flexible, and the driver would have more freedom
to optimize heterogeneous statements. The whole addBatch() / executeBatch()
at the statement level is pretty dumb. But it's what we have to work with.
The examples I posted inlined the arguments for clarity, but I guess it had
the opposite effect. ;-)
Clinton
From: Chris McMahon [mailto:cochrane68@hotmail.com]
Sent: February-29-08 7:10 PM
To: user-java@ibatis.apache.org
Subject: RE: iBATIS and Batch
This article:
http://java.sun.com/j2se/1.3/docs/guide/jdbc/spec2/jdbc2.1.frame6.html
suggests that you can pass heterogeneous SQL statements to the database
using the JDBC batch functionality. However, it was less clear that you
could do this with PreparedStatements.
Does iBATIS always use PreparedStatements even if there are no arguments to
pass in? In the examples you gave below, you didn't use arguments in your
SQL statements.
_____
From: clinton.begin@gmail.com
To: user-java@ibatis.apache.org
Subject: RE: iBATIS and Batch
Date: Fri, 29 Feb 2008 14:31:28 -0700
>> So you group things together by table?
iBATIS doesn't know what a table is. It groups them by (Prepared) SQL
Statement.
>> Does the JDBC driver not let you batch update across tables without
changing the order?
The batching features of JDBC are at the statement level. We maintain the
order of the statements, but otherwise it's exactly how the JDBC
documentation describes - which is quite vague and more up to the JDBC
driver than anything.
I believe it's as efficient as it can be, without losing the order of the
statements.
Clinton
From: Chris McMahon [mailto:cochrane68@hotmail.com]
Sent: February-29-08 2:07 PM
To: user-java@ibatis.apache.org
Subject: RE: iBATIS and Batch
So you group things together by table?
Does the JDBC driver not let you batch update across tables without changing
the order?
_____
From: clinton.begin@gmail.com
To: user-java@ibatis.apache.org
Subject: RE: iBATIS and Batch
Date: Fri, 29 Feb 2008 11:53:39 -0700
It batches based on the statement. So given this example:
INSERT INTO author (id,username, password, email, bio) VALUES
(1,'jim','********','jim@ibatis.apache.org','');
INSERT INTO author (id,username, password, email, bio) VALUES
(2,'sally','********','sally@ibatis.apache.org',null);
INSERT INTO blog (id,author_id,title) VALUES (1,1,'Jim Business');
INSERT INTO blog (id,author_id,title) VALUES (2,2,'Bally Slog');
INSERT INTO post (id,blog_id,created_on,subject,body) VALUES
(1,1,'2007-12-05-00.00.00','Corn nuts','I think if I never smelled another
corn nut, it would be too soon...');
INSERT INTO post (id,blog_id,created_on,subject,body) VALUES
(2,1,'2008-01-12-00.00.00','Paul Hogan on Toy Dogs','That''s not a dog.
THAT''s a dog!');
INSERT INTO post (id,blog_id,created_on,subject,body) VALUES
(3,2,'2007-12-05-00.00.00','Monster Trucks','I think monster trucks are
great...');
INSERT INTO post (id,blog_id,created_on,subject,body) VALUES
(4,2,'2008-01-12-00.00.00','Tea Parties','A tea party is no place to hold a
business meeting...');
These would be executed with 3 batches. If you mix up the order...
INSERT INTO author (id,username, password, email, bio) VALUES
(1,'jim','********','jim@ibatis.apache.org','');
INSERT INTO blog (id,author_id,title) VALUES (1,1,'Jim Business');
INSERT INTO post (id,blog_id,created_on,subject,body) VALUES
(1,1,'2007-12-05-00.00.00','Corn nuts','I think if I never smelled another
corn nut, it would be too soon...');
INSERT INTO post (id,blog_id,created_on,subject,body) VALUES
(2,1,'2008-01-12-00.00.00','Paul Hogan on Toy Dogs','That''s not a dog.
THAT''s a dog!');
INSERT INTO author (id,username, password, email, bio) VALUES
(2,'sally','********','sally@ibatis.apache.org',null);
INSERT INTO blog (id,author_id,title) VALUES (2,2,'Bally Slog');
INSERT INTO post (id,blog_id,created_on,subject,body) VALUES
(3,2,'2007-12-05-00.00.00','Monster Trucks','I think monster trucks are
great...');
INSERT INTO post (id,blog_id,created_on,subject,body) VALUES
(4,2,'2008-01-12-00.00.00','Tea Parties','A tea party is no place to hold a
business meeting...');
It would execute with 6 batches.
Doing it any other way would cause the statements to execute out of order,
which is a more serious concern and not correctable by the user of the
framework.
Clinton
From: Chris McMahon [mailto:cochrane68@hotmail.com]
Sent: February-29-08 11:43 AM
To: user-java@ibatis.apache.org
Subject: RE: iBATIS and Batch
We use the batch functionality frequently. How is iBATIS determining what
is homogeneous and what is heterogeneous? We have a mix of both straight
SQL updates and prepared statement updates.
_____
From: clinton.begin@gmail.com
To: user-java@ibatis.apache.org
Subject: RE: iBATIS and Batch
Date: Fri, 29 Feb 2008 08:58:43 -0700
Unfortunately no... that's possibly something we can consider for future
versions.
The challenge is that some people have the exact opposite problem. They
need the statements to maintain the order of execution. When initially
deciding who to help, I decided that preserving the order of execution was
more important than performance. I also assumed that if you're using a
mixed order of heterogeneous statements, you could probably find a way to
order them by type and send them through to make the most of batching.
Whereas the opposite wouldn't be true, as if I always just reordered the
statements for the sake of performance - there would be no way back. I
suppose we could support it as an option, but before that I have to ask:
Can you reorder your statements? If not, why not?
Clinton
From: chris.mccauley@gsa.gov [mailto:chris.mccauley@gsa.gov]
Sent: February-29-08 8:47 AM
To: user-java@ibatis.apache.org
Subject: iBATIS and Batch
Recently we have been implementing batched SQL calls using iBatis through
JDBC.
IT appears that iBatis will reorganize batch statements into homogenous
calls and treat them as separate batches....
The problem is that is if you do not serially add the homogenous statements,
each 'change' from homogeneity results in a separate batch...
Does this sound correct? Is there something we can do to control the
batching of heterogeneous calls?
Thank you,
Christopher
_____
Connect and share in new ways with Windows Live. Get it now!
<http://www.windowslive.com/share.html?ocid=TXT_TAGHM_Wave2_sharelife_012008
>
_____
Helping your favorite cause is as easy as instant messaging. You IM, we
give. Learn more.
<http://im.live.com/Messenger/IM/Home/?source=text_hotmail_join>
_____
Climb to the top of the charts! Play the word scramble challenge with star
power. Play now!
<http://club.live.com/star_shuffle.aspx?icid=starshuffle_wlmailtextlink_jan>