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 Vagisha Sharma <vs...@u.washington.edu> on 2009/04/26 07:16:02 UTC
What am I doing wrong: Ibatis vs JDBC insert
Hello,
I am trying to insert several thousand records into a table, and using
JDBC with DBCP connection pooling appears to be faster than using
iBATIS.
Using--
iBATIS 2.3.4
mySQL 5.1 (MyISAM storage engine)
In my tests I am inserting these records in a loop but in the actual
application these records will have to be inserted individually and
not in a loop. I cannot use bulk inserts or iBATIS' batch inserts.
Even though I have a test using the start and endTransactions outside
of the for loop this is not really an option for me. Curiously,
though, even this was not as fast as JDBC in my test. I've read some
of the "iBATIS is slow..." messages on this list and it appears that
there should be no reason for iBATIS to be slower than JDBC. I am
attaching my code in a zip file and would really appreciate any help.
Inserting 10,000 records in a for loop
1. with JDBC takes ~3.5 seconds
2. with iBATIS (start, commit and endTransaction at the beginning and
end of for loop) takes ~5 seconds
3. with iBATIS (no start, commit, endTransaction) takes ~9 seconds.
I would really like to know if I can speed up method 3.
Thanks a lot!
-----------------------------------------------------------------------------------------------
Here is my SqlMapConfig.xml
-----------------------------------------------------------------------------------------------
<sqlMapConfig>
<settings
useStatementNamespaces="true"
lazyLoadingEnabled="true"
/>
<transactionManager type="JDBC">
<dataSource type="DBCP">
<property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/>
<property name="JDBC.ConnectionURL" value="jdbc:mysql://
localhost/ibatis_test"/>
<property name="JDBC.Username" value="root"/>
<property name="JDBC.Password" value=""/>
<property name="JDBC.DefaultAutoCommit" value="true"/>
<property name="Pool.MaximumActiveConnections" value="100"/>
<property name="Pool.MaximumIdleConnections" value="30"/>
<property name="Pool.MaximumCheckoutTime" value="20000"/>
<property name="Pool.TimeToWait" value="20000"/>
</dataSource>
</transactionManager>
<sqlMap resource="TestSqlMap.xml"/>
</sqlMapConfig>
-----------------------------------------------------------------------------------------------
TestSqlMap.xml
-----------------------------------------------------------------------------------------------
<sqlMap namespace="Test">
<typeAlias alias="TestBean" type="TestBean" />
<parameterMap class="TestBean" id="testParam">
<parameter property="intValue" jdbcType="INTEGER" />
<parameter property="doubleValue" jdbcType="DOUBLE" />
<parameter property="stringValue" jdbcType="VARCHAR" />
</parameterMap>
<insert id="insert" parameterMap="testParam">
INSERT INTO testTable (
intValue,
doubleValue,
stringValue)
VALUES (?,?,?)
<selectKey resultClass="int" >
select last_insert_id() as id
</selectKey>
</insert>
</sqlMap>
-----------------------------------------------------------------------------------------------
iBATIS insert
-----------------------------------------------------------------------------------------------
public void ibatisInsert(SqlMapClient sqlMap, int count) throws
SQLException {
for(int i = 0; i < count; i++) {
TestBean bean = createTestBean();
Integer autogenkey =
(Integer)sqlMap.insert("Test.insert", bean);
}
}
-----------------------------------------------------------------------------------------------
JDBC insert
-----------------------------------------------------------------------------------------------
public void jdbcInsert(DataSource ds, int count) throws SQLException {
String sql = "INSERT INTO testTable (intValue, doubleValue,
stringValue) VALUES(?,?,?)";
for(int i = 0; i < count; i++) {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
TestBean bean = createTestBean();
try {
conn = ds.getConnection();
stmt = conn.prepareStatement(sql);
stmt.setInt(1, bean.getIntValue());
stmt.setDouble(2, bean.getDoubleValue());
stmt.setString(3, bean.getStringValue());
stmt.execute();
rs = stmt.getGeneratedKeys();
if (rs.next()) {
int autogenkey = rs.getInt(1);
} else {
// throw an exception from here
}
rs.close();
}
finally {
try {if(conn != null) conn.close();}
catch(SQLException e){}
try {if(stmt != null) stmt.close();}
catch(SQLException e){}
try {if(rs != null) rs.close();} catch(SQLException e)
{}
}
}
}
Re: What am I doing wrong: Ibatis vs JDBC insert
Posted by Nathan Maves <na...@gmail.com>.
I love apples to oranges comparisons :)
First off you are not using a connection pool in your jdbc example. Even
though your ibatis code will be run synchronously you will still incur that
overhead.
I am guessing that you are trying to prove to someone that ibatis is just as
fast as jdbc. If that is the case give up. We, the development team, and
the community at large have tested and tested this and the speed difference
is so small it is not worth looking at.
If you are really trying to speed up this example try to batch your
statements. You will see a huge performance gain.
Nathan
On Sat, Apr 25, 2009 at 11:16 PM, Vagisha Sharma
<vs...@u.washington.edu>wrote:
> Hello,
>
> I am trying to insert several thousand records into a table, and using JDBC
> with DBCP connection pooling appears to be faster than using iBATIS. Using--
> iBATIS 2.3.4
> mySQL 5.1 (MyISAM storage engine)
> In my tests I am inserting these records in a loop but in the actual
> application these records will have to be inserted individually and not in
> a loop. I cannot use bulk inserts or iBATIS' batch inserts.
> Even though I have a test using the start and endTransactions outside of
> the for loop this is not really an option for me. Curiously, though, even
> this was not as fast as JDBC in my test. I've read some of the "iBATIS is
> slow..." messages on this list and it appears that there should be no
> reason for iBATIS to be slower than JDBC. I am attaching my code in a zip
> file and would really appreciate any help.
>
> Inserting 10,000 records in a for loop
> 1. with JDBC takes ~3.5 seconds
> 2. with iBATIS (start, commit and endTransaction at the beginning and end
> of for loop) takes ~5 seconds
> 3. with iBATIS (no start, commit, endTransaction) takes ~9 seconds.
>
> I would really like to know if I can speed up method 3.
>
> Thanks a lot!
>
>
>
>
>
>
> -----------------------------------------------------------------------------------------------
> Here is my SqlMapConfig.xml
>
> -----------------------------------------------------------------------------------------------
> <sqlMapConfig>
> <settings
> useStatementNamespaces="true"
> lazyLoadingEnabled="true"
> />
>
>
> <transactionManager type="JDBC">
> <dataSource type="DBCP">
> <property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/>
> <property name="JDBC.ConnectionURL" value=
> "jdbc:mysql://localhost/ibatis_test"/>
> <property name="JDBC.Username" value="root"/>
> <property name="JDBC.Password" value=""/>
>
>
> <property name="JDBC.DefaultAutoCommit" value="true"/>
> <property name="Pool.MaximumActiveConnections" value="100"/>
> <property name="Pool.MaximumIdleConnections" value="30"/>
> <property name="Pool.MaximumCheckoutTime" value="20000"/>
> <property name="Pool.TimeToWait" value="20000"/>
> </dataSource>
> </transactionManager>
>
> <sqlMap resource="TestSqlMap.xml"/>
>
> </sqlMapConfig>
>
>
> -----------------------------------------------------------------------------------------------
> TestSqlMap.xml
>
> -----------------------------------------------------------------------------------------------
> <sqlMap namespace="Test">
>
>
> <typeAlias alias="TestBean" type="TestBean" />
>
>
> <parameterMap class="TestBean" id="testParam">
> <parameter property="intValue" jdbcType="INTEGER" />
> <parameter property="doubleValue" jdbcType="DOUBLE" />
> <parameter property="stringValue" jdbcType="VARCHAR" />
> </parameterMap>
>
>
>
> <insert id="insert" parameterMap="testParam">
> INSERT INTO testTable (
> intValue,
> doubleValue,
> stringValue)
> VALUES (?,?,?)
> <selectKey resultClass="int" >
> select last_insert_id() as id
> </selectKey>
> </insert>
>
>
> </sqlMap>
>
>
> -----------------------------------------------------------------------------------------------
> iBATIS insert
>
> -----------------------------------------------------------------------------------------------
> public void ibatisInsert(SqlMapClient sqlMap, int count) throwsSQLException {
>
> for(int i = 0; i < count; i++) {
> TestBean bean = createTestBean();
> Integer autogenkey = (Integer)sqlMap.insert("Test.insert",
> bean);
> }
> }
>
>
> -----------------------------------------------------------------------------------------------
> JDBC insert
>
> -----------------------------------------------------------------------------------------------
> public void jdbcInsert(DataSource ds, int count) throws SQLException {
>
>
> String sql = "INSERT INTO testTable (intValue, doubleValue,
> stringValue) VALUES(?,?,?)";
>
>
> for(int i = 0; i < count; i++) {
> Connection conn = null;
> PreparedStatement stmt = null;
> ResultSet rs = null;
> TestBean bean = createTestBean();
>
>
> try {
> conn = ds.getConnection();
> stmt = conn.prepareStatement(sql);
> stmt.setInt(1, bean.getIntValue());
> stmt.setDouble(2, bean.getDoubleValue());
> stmt.setString(3, bean.getStringValue());
> stmt.execute();
>
>
> rs = stmt.getGeneratedKeys();
>
> if (rs.next()) {
> int autogenkey = rs.getInt(1);
> } else {
> // throw an exception from here
> }
>
> rs.close();
> }
> finally {
> try {if(conn != null) conn.close();} catch(SQLException
> e){}
> try {if(stmt != null) stmt.close();} catch(SQLException
> e){}
> try {if(rs != null) rs.close();} catch(SQLException e){}
> }
> }
> }
>
>
>
>
RE: What am I doing wrong: Ibatis vs JDBC insert
Posted by vsharma <vs...@u.washington.edu>.
Thank you for replying! I tried your suggestion but it did not make any
difference. Setting the value of this property to "false" did not make it
worse either!
-V
Try adding:
<property name="Pool.poolPreparedStatements" value="true"/>
or equivalent to your config file.
I ran into this very thing a while ago and found this to be an issue if is
not specified.
Please advise of results.
Regards,
M. Goodell
--
View this message in context: http://www.nabble.com/What-am-I-doing-wrong%3A--Ibatis-vs-JDBC-insert-tp23239348p23239720.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
RE: What am I doing wrong: Ibatis vs JDBC insert
Posted by "M. Goodell" <mg...@comcast.net>.
Try adding:
<property name="Pool.poolPreparedStatements" value="true"/>
or equivalent to your config file.
I ran into this very thing a while ago and found this to be an issue if is
not specified.
Please advise of results.
Regards,
M. Goodell
-----Original Message-----
From: Vagisha Sharma [mailto:vsharma@u.washington.edu]
Sent: Saturday, April 25, 2009 11:16 PM
To: user-java@ibatis.apache.org
Subject: What am I doing wrong: Ibatis vs JDBC insert
Hello,
I am trying to insert several thousand records into a table, and using
JDBC with DBCP connection pooling appears to be faster than using iBATIS.
Using--
iBATIS 2.3.4
mySQL 5.1 (MyISAM storage engine)
In my tests I am inserting these records in a loop but in the actual
application these records will have to be inserted individually and not in
a loop. I cannot use bulk inserts or iBATIS' batch inserts.
Even though I have a test using the start and endTransactions outside of
the for loop this is not really an option for me. Curiously, though, even
this was not as fast as JDBC in my test. I've read some of the "iBATIS is
slow..." messages on this list and it appears that there should be no
reason for iBATIS to be slower than JDBC. I am attaching my code in a zip
file and would really appreciate any help.
Inserting 10,000 records in a for loop
1. with JDBC takes ~3.5 seconds
2. with iBATIS (start, commit and endTransaction at the beginning and end
of for loop) takes ~5 seconds
3. with iBATIS (no start, commit, endTransaction) takes ~9 seconds.
I would really like to know if I can speed up method 3.
Thanks a lot!
--------------------------------------------------------------------------
---------------------
Here is my SqlMapConfig.xml
--------------------------------------------------------------------------
---------------------
<sqlMapConfig>
<settings
useStatementNamespaces="true"
lazyLoadingEnabled="true"
/>
<transactionManager type="JDBC">
<dataSource type="DBCP">
<property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/>
<property name="JDBC.ConnectionURL"
value="jdbc:mysql://localhost/ibatis_test"/>
<property name="JDBC.Username" value="root"/>
<property name="JDBC.Password" value=""/>
<property name="JDBC.DefaultAutoCommit" value="true"/>
<property name="Pool.MaximumActiveConnections" value="100"/>
<property name="Pool.MaximumIdleConnections" value="30"/>
<property name="Pool.MaximumCheckoutTime" value="20000"/>
<property name="Pool.TimeToWait" value="20000"/>
</dataSource>
</transactionManager>
<sqlMap resource="TestSqlMap.xml"/>
</sqlMapConfig>
--------------------------------------------------------------------------
---------------------
TestSqlMap.xml
--------------------------------------------------------------------------
---------------------
<sqlMap namespace="Test">
<typeAlias alias="TestBean" type="TestBean" />
<parameterMap class="TestBean" id="testParam">
<parameter property="intValue" jdbcType="INTEGER" />
<parameter property="doubleValue" jdbcType="DOUBLE" />
<parameter property="stringValue" jdbcType="VARCHAR" />
</parameterMap>
<insert id="insert" parameterMap="testParam">
INSERT INTO testTable (
intValue,
doubleValue,
stringValue)
VALUES (?,?,?)
<selectKey resultClass="int" >
select last_insert_id() as id
</selectKey>
</insert>
</sqlMap>
--------------------------------------------------------------------------
---------------------
iBATIS insert
--------------------------------------------------------------------------
---------------------
public void ibatisInsert(SqlMapClient sqlMap, int count) throws
SQLException {
for(int i = 0; i < count; i++) {
TestBean bean = createTestBean();
Integer autogenkey = (Integer)sqlMap.insert("Test.insert",
bean);
}
}
--------------------------------------------------------------------------
---------------------
JDBC insert
--------------------------------------------------------------------------
---------------------
public void jdbcInsert(DataSource ds, int count) throws SQLException {
String sql = "INSERT INTO testTable (intValue, doubleValue,
stringValue) VALUES(?,?,?)";
for(int i = 0; i < count; i++) {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
TestBean bean = createTestBean();
try {
conn = ds.getConnection();
stmt = conn.prepareStatement(sql);
stmt.setInt(1, bean.getIntValue());
stmt.setDouble(2, bean.getDoubleValue());
stmt.setString(3, bean.getStringValue());
stmt.execute();
rs = stmt.getGeneratedKeys();
if (rs.next()) {
int autogenkey = rs.getInt(1);
} else {
// throw an exception from here
}
rs.close();
}
finally {
try {if(conn != null) conn.close();} catch(SQLException
e){}
try {if(stmt != null) stmt.close();} catch(SQLException
e){}
try {if(rs != null) rs.close();} catch(SQLException e){}
}
}
}
No virus found in this outgoing message.
Checked by AVG - www.avg.com
Version: 8.5.287 / Virus Database: 270.12.4/2080 - Release Date: 04/25/09
08:29:00