Hi,

I am using hibernate with JPA and Spring to make calls to stored procedures in a mysql db. With the built in connection provider the performance is good. When I add:

<property name='hibernate.connection.provider_class'>org.hibernate.connection.C3P0ConnectionProvider</property>

to my hibernate.cfg.xml the performance slows down by 10x. I have adjusted the C3PO config settings without success.

The code is simple and looks like this:

Query query = session.getNamedQuery( "getSummaryCharacteristicCountsWithAnd" );
query.setParameter( "personaIds", csvPersonaIds );
List<CharacteristicCountImpl> characteristicCounts = query.list();

the session is obtained from the hibernate session factory. The query returns around 20 rows and about 10 columns per row.

The objects returned are simple flat objects so a single row is mapped to each object with no fancy hibernate stuff. I'm using named queries defined in the returned object.

How can I pool connections with hibernate and not take a 10x performance hit? The hibernate built-in connection provider will not work in production because after many hours idle we are no longer able to get connections. Is there a better connection provider or some other solution to pool connections?

I'm using hibernate 3.6, c3p0 is provided by hibernate 3.6 (v0.9.1), mysql java connector 5.1.6.

Any help is much appreciated.

Thanks,
Patrick