Is my Java Hibernate application using Prepared Statement Pooling?

Question

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)

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);
}

Answer 1

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.