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:vsha...@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
