Keep DB connection using JPA/Hibernate

Question

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??

how are transactions managed? manually, spring, JavaEE?

Bozho
– Bozho

2010-08-13 12:46:57 +00:00
Commented Aug 13, 2010 at 12:46 — Bozho
– Bozho, Commented Aug 13, 2010 at 12:46

Pascal Thivent · Accepted Answer · 2010-08-13 15:55:43Z

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

Collectives™ on Stack Overflow

Keep DB connection using JPA/Hibernate

1 Answer 1

Resources

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Resources

Comments

Your Answer

Sign up or log in

Post as a guest

Related