Hibernate & Connection Pooling

[Kevin P Smith]

Hi

I've been looking at using Hibernate for my database acivity on a new system I'm writing, but there are stil a couple of things I need to find out first

One is in regards to load/stress with large scale (possibly 5 million records) databases, as my understaning is Hibernate basically creates a Bean for each result from the ResultSet (in theory a user could run a 'SELECT * FROM Table' and return EVERYTHING!!!).

Second thing is in egards to Connection Pooling. I have a connection pool I found off the Net for standard JDBC DB'ing, but I was wondering about Hibernate and Connection Pools.
I've read a little abotu Hibernate and it looks like it has a few options that already exists for Connection Pooling, including C3P0, Apache's DBCP library, and Proxool.

I was just wonderign which is people recommend and why?

Cheers

K

[Paul Sturrock]

(in theory a user could run a 'SELECT * FROM Table' and return EVERYTHING!!!).

They could do this. But that is true of any database system, regardless of whether you use Hibernate or not. If they do do this, the performance bottleneck will probably be the database itself, before any obejct are created by Hibernate. There are a number of ways you can stop this sort of this happening. For example Hibernate support a limit on the size of the result set returned, by using whatever limiting stategy the database provides. This aside, its normally up to your application to prevent the user doing this. A DBA can control performance (by setting maximin CPU time for a query for example), but more normally your applciation should prevent such big queries anyway.

[Kevin P Smith]

Cheers for that, but in regards to this first issue, the user (according to spec) will have the ability to run this type of query. But I have been looking at ways to get around this

The second question was the one I was more interested in, the connection pooling. Looks like Hibernate's own ConnPool isn't rated much (for Dev perposes) so which of the others do people recommend? C3P0 looks popular. Are there any issues with any of the 3 mentioned, performance issues etc etc?

Cheers

[Mark Spritzler]

"Keith S",
Welcome to the JavaRanch.

We're a friendly group, but we do require members to have valid display names.

Display names must be two words: your first name, a space, then your last name. Fictitious names are not allowed.

Please edit your profile and correct your display name since accounts with invalid display names get deleted.

[Mark Spritzler]

Well, if you are in a container, Hibernate will use the Connection pool implementation of the container. C3PO is used when you are running Hibernate outside of a container.

Also, like Paul said, you can set the max result set/pagination settings for the number of records retreived at a time. I know that there isn't a single user that can look at 5 million records at once anyway, so your design of your UI, regardless of Hibernate, will have to take that into account.

You really do want Hibernate to do some sort of pagination for records, because with Hibernate or JDBC 5 million record objects either in a ResultSet or you Java Objects will be a lot of memory, and I doubt you servers will be able to handle that without running out of memory.

So I highly recommend you go to your architect/designer/requirements gatherer, and ask them why it makes sense to let a user query 5 million records at once when they won't even be able to read all 5 million records in one day or one week, I doubt in their lifetime.

Mark

[Mathias Nilsson]

I use C3PO and it works fine. You can set it in Hibernate.cfg.xml by using the following properties.

<property name="c3p0.min_size">3</property>
<property name="c3p0.max_size">5</property>
<property name="c3p0.timeout">1800</property>
<property name="c3p0.max_statements">50</property>

You can use C3PO in a container.

[Kevin P Smith]

Originally posted by Mark Spritzler:
Well, if you are in a container, Hibernate will use the Connection pool implementation of the container. C3PO is used when you are running Hibernate outside of a container.

Also, like Paul said, you can set the max result set/pagination settings for the number of records retreived at a time. I know that there isn't a single user that can look at 5 million records at once anyway, so your design of your UI, regardless of Hibernate, will have to take that into account.

You really do want Hibernate to do some sort of pagination for records, because with Hibernate or JDBC 5 million record objects either in a ResultSet or you Java Objects will be a lot of memory, and I doubt you servers will be able to handle that without running out of memory.

So I highly recommend you go to your architect/designer/requirements gatherer, and ask them why it makes sense to let a user query 5 million records at once when they won't even be able to read all 5 million records in one day or one week, I doubt in their lifetime.

Mark

I have been looking at the 'searching 5 million records' thing and I'm not actually gonna perform a search on 5 million results, instead I'm going for a 'summary' table. See all the records are of people, each person is in a region, in a country, on a continent.
So i was thinking of a summary table that displays the number of people for each (continent, country, region) then if you click on a continent you get all the countries there, click on a country you get the region click the region you get the people (these will be SQL queries)!

This way I think the biggest search performed would be for all people in Europe (about 1.5m) still big (and very unlikely) but do-able.

As I say, I have to allow for what could happen rather than what I'd expect to happen.

This seems like the best solution.

As for the Connection Pooling, C3P0 looks like the more commonly used.

[Mark Spritzler]

Originally posted by Keith S:


I have been looking at the 'searching 5 million records' thing and I'm not actually gonna perform a search on 5 million results, instead I'm going for a 'summary' table. See all the records are of people, each person is in a region, in a country, on a continent.
So i was thinking of a summary table that displays the number of people for each (continent, country, region) then if you click on a continent you get all the countries there, click on a country you get the region click the region you get the people (these will be SQL queries)!

This way I think the biggest search performed would be for all people in Europe (about 1.5m) still big (and very unlikely) but do-able.

As I say, I have to allow for what could happen rather than what I'd expect to happen.

This seems like the best solution.

As for the Connection Pooling, C3P0 looks like the more commonly used.

For those tables that are country/region, etc, they are aggregate queries, not a Query that will return 1.5 different rows. And if you still want to use Pagination for any UI, simply that no user can look at that many rows.

Mark

[Mark Spritzler]

Originally posted by Keith S:


I have been looking at the 'searching 5 million records' thing and I'm not actually gonna perform a search on 5 million results, instead I'm going for a 'summary' table. See all the records are of people, each person is in a region, in a country, on a continent.
So i was thinking of a summary table that displays the number of people for each (continent, country, region) then if you click on a continent you get all the countries there, click on a country you get the region click the region you get the people (these will be SQL queries)!

This way I think the biggest search performed would be for all people in Europe (about 1.5m) still big (and very unlikely) but do-able.

As I say, I have to allow for what could happen rather than what I'd expect to happen.

This seems like the best solution.

As for the Connection Pooling, C3P0 looks like the more commonly used.

For those tables that are country/region, etc, they are aggregate queries, not a Query that will return 1.5 different rows. And if you still want to use Pagination for any UI, simply that no user can look at that many rows.

Mark