ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nathan Maves <nathan.ma...@gmail.com>
Subject Re: What am I doing wrong: Ibatis vs JDBC insert
Date Tue, 28 Apr 2009 22:04:03 GMT
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
<vsharma@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){}
>             }
>         }
>     }
>
>
>
>

Mime
View raw message