ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Vagisha Sharma <vsha...@u.washington.edu>
Subject What am I doing wrong: Ibatis vs JDBC insert
Date Sun, 26 Apr 2009 05:16:02 GMT
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) 
{}
             }
         }
     }


Mime
View raw message