Is my Java Hibernate application using Prepared Statement Pooling?

user_1480018 0
Viewed 847

I am using Hibernate 4.3.11.Final and H2 1.3.172 database and I was profiling my application on a slow linux box and finding it was spending more time on a particular SQL INSERT then anything else. It also seems that the prepared statements are not being cached since it seemed that the number of prepared statements was about the same as the number of statements executed.

Have I interpreted this right (I am using Yourkit Profiler)

enter image description here

My HibernateUtil class configure as follows

public static Configuration getInitializedConfiguration()
    {
        Configuration config = new Configuration();

        config.setProperty(Environment.DRIVER,"org.h2.Driver");
        config.setProperty(Environment.URL,"jdbc:h2:"+Db.DBFOLDER+"/"+Db.DBNAME+";FILE_LOCK=SOCKET;MVCC=TRUE;DB_CLOSE_ON_EXIT=FALSE;CACHE_SIZE=50000");
        config.setProperty(Environment.DIALECT,"org.hibernate.dialect.H2Dialect");
        System.setProperty("h2.bindAddress", InetAddress.getLoopbackAddress().getHostAddress());
        config.setProperty("hibernate.connection.username","jaikoz");
        config.setProperty("hibernate.connection.password","jaikoz");
        config.setProperty("hibernate.c3p0.numHelperThreads","10");
        config.setProperty("hibernate.c3p0.min_size","20");
        //Consider that if we have lots of busy threads waiting on next stages could we possibly have alot of active
        //connections.
        config.setProperty("hibernate.c3p0.max_size","200");
        config.setProperty("hibernate.c3p0.timeout","300");
        config.setProperty("hibernate.c3p0.maxStatementsPerConnection","50");
        config.setProperty("hibernate.c3p0.idle_test_period","3000");
        config.setProperty("hibernate.c3p0.acquireRetryAttempts","10");
        addEntitiesToConfig(config);
        return config;
    }

I wonder if I have configured it incorrectly, what is particularly confusing is that the c3po documentation does quite marry up with the Hibernate config regarding the name of some of the parameters.

i.e it is max_size or max_pool_size

Its a single user multi threaded application, and ideally I want all prepared statements cached for the duration of the application, since there are only about 50 different statements.

As I understand it every time I do 

session = HibernateUtil.beginTransaction();

that will get a connection from the pool, and if that particular connection has previously prepared the statement that is now required then it can use that prepared statement without having to compile a new one.

If the prepared statement does not exist then it is prepared.

If there are already fifty prepared statements for this connection then the oldest one will be dropped.

This particular query that is taking up so more time is used as follows

public static void saveMatchedToRelease(Session session,Integer reportId, Integer recNo, SongFieldKey songFieldKey, SongChangeType type, String original, String edited)
{
    SongChanges sc = new SongChanges();
    sc.setReportId(reportId);
    sc.setRecNo(recNo);
    sc.setField(songFieldKey);
    sc.setType(type);
    sc.setOriginalValue(original);
    sc.setNewValue(edited);
    session.save(sc);
}
c3p0 profiler hibernate java
3 Answers

In H2 prepared statements are cached on connection level. With hibernate.c3p0.max_size=200 there is a chance that you have so many open connections that each time the user performs operation he will get a different H2 connection.

Local in-memory H2 has a minimal connection cost comparing to other RDBMS. Try removing C3P0 and test with a single H2 connection. This should confirm that prepared statements are cached by JDBC driver.

In most situations less connections is better. In your case, with a single user and multiple threads, it's unlikely that your machine has 200 CPUs to take full advantage of hibernate.c3p0.max_size=200.

user_16025550

I dont think the caching is the problem. If you open a new transaction with every insert and you always insert into your songs table with different threads, maybe you block yourself with open transactions. Multithreading can easily lead to errors especially with open database transactions.

user_6469120

Since JDBC 3.0, setting setMaxStatements(n) with n as positive number will enable statement pooling and thereby caching.

If anyone working on Oracle JDBC driver, you can verify the caching by any of the below methods

On Connection,

if (conn.getImplicitCachingEnabled())
  System.out.println("\nimplicit caching enabled"); 
else
  System.out.println("\nimplicit caching disabled");

On Statement,

int creationState = stmt.creationState();
switch(creationState) {
case 0:
System.out.println("\nCreation state: new");
break;
case 1:
System.out.println("\nCreation state: from the implicit cache"); 
break;
case 2:
System.out.println("\nCreation state: from the explicit cache"); 
break;
}

Reference : Oracle Statement Pooling and Caching

user_48210270
Related