I'm using Liferay 4.2.1, and found some weird result with hibernate query.

There's a query with an outer join, and here is the portion of the hql:



This part of the code does not seem to be much of problem, I'm running it on my machine (slow machine), with 1GB of memory, there are 2000 rows of data, and getting all of them requires about 1 second.

Now, when I added the following for pagination:



and then



And setting max to 10, it takes 3 to 5 times longer than if I just retrieve the whole list (without setFirstResult() and setMaxResults()). It takes 3 to 5 seconds to get 10 records. This does not make any sense.

As the amount of data grows, it is getting slower and slower. If I paginate over 100 records, the performance is ok, but still slower than getting the whole 100 records. But as data grow, it is getting slower.

Using setFirstReuslt() and setMaxResults() is the default method for paginating, but this is killing us.

It takes 3 to 5 time longer to get 10 records than retrieving the whole 2000 records!!! Now I can get the whole list, then spending a few more millisecs using sublist() to get the same 10 records, and I'm still better off, time-wise. But this is not the way to go, resources-wise.

I'm using:

OS: Linux (Ubuntu 07.04)
Liferay 4.2.1
PostgreSQL 8.1.10
Java 1.5.0_08

Could someone give a hint what is going on here?

thanks

weird coder

I suspect the pagination through the sorted by desc displayDate. Is this column indexed?

displayDate is indexed, and as I described above, the whole query's performance is ok if I retrieve everything. It's really slow only when I do pagination.

The thing that does not make sense is that pagination is killing the system, performance-wise. It is supposed to do the opposite. The worst part is that this is the standard recommended pagination method for Hibernate. How can that be a standard and recommended method, and yet, with a such lousy performance?

For the same amount of data, SQL would return the desired data (same as what we do with HQL) in a fraction of second. Give that hibernate needs to create the objects etc, I can accept that the whole deal takes about 1 sec (although this is still slow), but when it takes 3 to 5 times slower, this is not going to fly.

Could you post the generated sql and check/try it on the database?

I finally figured that out. Hibernate generates a nasty n+1 sql query, looping on every row of the table.

The tables journalarticle and journalcategory have a many-to-many relationship, and hibernate creates an intermediate table articles_categories between them. The sql statement generated looks basically like the hql. and if I don't do pagination, that's just fine. If I want pagination, Hibernate should have just added

at the end of the sql statement, and would have a nice, clean solution. Instead, Hibernate will fetch every single row, loop every row found on the intermediate table articles_categories. Therefore, if I have 2000 records, Hibernate would query the database 2001 times!!!

I just get rid off the hql, and write my own native sql (which defeats the purpose of using Hibernate, really!), and now paginating time is less than 0.2 second, instead of 3 to 5 seconds. And for data less than 200 rows, the time is about 30 millisecs.

Now, I turn on every place where we have many-to-many relationship, and found that Hibernate is doing this kind of stupid things all over the place, even though we are following the best practices recommended.

I'm less and less impressed with Hibernate now.

The trouble is, in a lot of many-to-one relationship, that also results in a huge number of queries. For example:


And now, with the following query:

This result into huge number of queries, regardless of whether lazy is set to false or true, this has no effect. It will query the table cool_thing first, then for every row found, it will query the table jewel. For large number of data, the number of queries just kill the database (and yes, I have the columns indexed).

Now, I have really huge doubt with Hibernate. It's fine when you have simple single-table thing, but as soon as you have a bit complicated stuff, it just doesn't fly. In this case, OR-mapping just sucks big time, and the structure above is a pretty simple example, compared to what we have.

Could someone tell what's wrong with the structure and query above? How would Hibernate gurus recommend in this case?