Keep DB connection using JPA/Hibernate

Question

Hi there...

I am using JPA and Hibernate in an application. (with PostgreSQL as DBMS)

I execute a lot of select-statements to get items stored in the DB by their name.

I use the following Code:

    //em is the javax.persistence.EntityManager Object.
    Query q = em.createQuery("Select i from Item i where i.name = :name");

    q.setParameter("name", nameParam);

    List<NavigationItem> results = q.getResultList();

So, my Problem is, that i lose a lot of time by connecting to the DB, preparing the statement and closing the connection. I used Eclipse TPTP to track which methods costs the most time: (per minute)

com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(): ~22s com.mchange.v2.c3p0.impl.NewProxyConnection().close(): ~16s com.mchange.v2.c3p0.impl.NewProxyConnection().prepareStatement(String): ~12s com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource().getConnetction(): ~6s com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.close(): ~4s

The execution of the Query, needs his time, that's ok, but i think the other methods needs more time than they should. If i could keep the connection open for several (or several hundreds) executions, the application would be a lot faster.

So my Question is: How can i keep this connection open??

Answer 1

Profiling does have some impact on performance but still, the execution times reported her are insanely sloooowww and this is unexpected. I mean, you're using a connection pool and obtaining a connection and closing should thus be almost instantaneous, these operations shouldn't be a problem at all - that's somehow the point of using a connection pool. And the time to prepare a statement is ridiculously high.

There is something wrong somewhere and keeping the connection open is definitely not the solution (pooling should just work). But there is not enough information for a diagnostic. So, could you please add some details about:

• The context (out-container? in-container?)
• The connection pool configuration.
• The code surrounding the snippet you're showing.

As a side note, you should favor using named queries (they will most often get precompiled by the JPA provider to avoid the overhead of parsing the JPQL and generating the SQL each time). But this is not the problem here. The root cause must be solved.

Resources

• HowTo configure the C3P0 connection pool