Atomikos Forum

Hello,

I am using Spring/Hibernate/JPA. I've got a test case where 10,000 objects are written to the database, with no threading (beyond the main thread). If I swap out Atomikos for a non-JTA TransactionManager and C3P0 (the connection pooling plugin that also supports caching statements) I see a ten fold improvement in performance, ie time to insert the objects.

Does Atomikos have any statement caching facility? Running JVisualVM suggests the delay is statement related.

Here's the relevant Spring config:

    <!-- Set up Atomikos JTA stuff -->
    <bean id="atomikosTransactionManager" 
          class="com.atomikos.icatch.jta.UserTransactionManager" 
          init-method="init" destroy-method="close">     
      <property name="forceShutdown" value="true" />
    </bean>         
    <bean id="atomikosUserTransaction" 
      class="com.atomikos.icatch.jta.UserTransactionImp">     
      <property name="transactionTimeout" value="300" />
    </bean>             
    <!--
        Non-pooled Postgres XA datasource.
        
        You must set max_prepared_transactions to some figure other than 0
        in postgresql.conf for XA transactions to work.    
    -->
    <bean id="xaDataSource" class="${db.ildb.jdbc.driverClassName}">
        <property name="serverName" value="${db.ildb.jdbc.hostname}" />
        <!-- For MySQL, use port -  I know, this needs fixing -->
        <property name="portNumber" value="${db.ildb.jdbc.port}" />
        <property name="user" value="${db.ildb.jdbc.username}" />
        <property name="password" value="${db.ildb.jdbc.password}" />
        <property name="databaseName" value="${db.ildb.jdbc.databasename}" />
    </bean>
    
    <!-- JTA aware datasource that also happens to provide pooling -->
    <bean id="remoteDataSource" class="com.atomikos.jdbc.AtomikosDataSourceBean"
      init-method="init" destroy-method="close">
      <!-- Set an arbitrary but unique name for the datasource -->
      <property name="uniqueResourceName"><value>IL.XA.DB</value></property>
      <property name="xaDataSource" ref="xaDataSource" />
      <property name="minPoolSize" value="${db.ildb.connpool.minPoolSize}"/>
      <property name="maxPoolSize" value="${db.ildb.connpool.maxPoolSize}"/>
      <property name="loginTimeout" value="${db.ildb.connpool.loginTimeout}"/>
      <property name="testQuery" value="select count(*) from ${db.ildb.jdbc.databasename}.ctp_event" />
    </bean>        

Any input gratefully received.

Thanks

Hi,

We don't currently cache prepared statements because nobody every reported any problems in that area...

AFAIK prepared statements are server-side caching of SQL parsing results and hence have little client-side requirements. Can you clarify in case I misunderstood?

Thanks!

Hello

Thanks for your reply. Let me be the first to report a potential problem.

Using a simple for loop to write objects to a database (ie via Hibernate), when removing Atomikos and putting in place C3P0 (that caches statements), we see a ten fold improvement in performance over Atomikos.

Hibernate used to contain some statement caching functionality (set via properties) but the docs say this has now been deprecated.

I believe statements (PreparedStatements) are cached at a DB server level, but it looks like we need it at the application level too. I've run jvisualvm over the Atomikos test and something like 50% of the time is spent preparing statements through the JDBC driver.

I've spent the last few hours trying to glue AtomikosDataSourceBean, which accepts an XA datasource, to C3P0, which provides a non-XA datasource.

Perhaps you could be encouraged to set up a simple JPA/Spring/Hibernate and Atomikos/C3P0 test to see if the problem is real?


John