February 08, 2006

Hibernate and efficient queries

First post of the year (bad boy)...

I've spent a lot of this year so far jumping between lots of different things. I've started dipping into the new SiteBuilder code which is far more familiar as it is now Spring/Hibernate based rather than Struts/EJB.

I've also as usual been working on Single Sign-On and BlogBuilder.

As the complexity of BlogBuilder grows and our page views grows (now averaging more than 50,000 proper real people page views per day), it has become more and more important to optimise BlogBuilder for better performance.

Hongfeng our resident Oracle expert pointed me in the direction of quite a lot of particularly bad and slow pieces of SQL that were being generated out of BlogBuilder. The problem with BlogBuilder is that it is very very dynamic. We do not serve any static pages as every single page is customised to the currently logged in user as every blog/image/entry has its own permissions. There are also just a lot of different views on the blogs data; daily views, monthly views, favourites views, entries by tags, blogs by group, images by day, etc…

When using Hibernate 2 I did most of these queries with HQL, and it worked quite well, but I'm starting to feel the strain as some of the queries got more and more complicated.

With Hibernate 3 I can now take advantage of the Criteria API, which is quite nice for building complicated queries, but it still has some problems so I've now got a mix of HQL, Criteria and plain old SQL when a particularly complicated aggregation is needed.

Don't forget to turn on query caching and specifically tell your criteria and queries to cache as although the documentation says that for most queries caching doesn't make much difference, I've found it can make a huge difference.

Another little trick is to be careful with date range queries. If you want to do something like find items based on the current time, round your time to the nearest hour or minute rather than passing in a date with second or millisecond accuracy as this will prevent those queries being cached for more than a second…not a lot of good.

Another trick when moving from Hibernate 2 to Hibernate 3 is that you used to have to do "query.iterate().next()" to get a result when you knew there was just a single result (such as a count query), but now there is the uniqueResult() method. It is important to switch over because the uniqueResult() calls get cached, but the iternate().next() ones don't.


- 2 comments by 1 or more people Not publicly viewable

  1. Another huge win (which I keep meaning to blog about) is prefetching the object graph in a single query, using joins. I did this on SBR and the performance improvements were very dramatic.

    In SBR, the query that loads the page joins the page, and all data required to render the page in a single query. Cut the time spent in SQL by 10.

    08 Feb 2006, 15:37

  2. Yeah, I should also have mentioned that I added a lot of "batch-size=x" changes to my mappings which has greatly reduced the number of queries. I'll have to look into the prefetching a bit more as well as I generally make stuff lazy, but if you know you're going to need it, you may as well get it in one go at the start.

    08 Feb 2006, 15:44


Add a comment

You are not allowed to comment on this entry as it has restricted commenting permissions.

February 2006

Mo Tu We Th Fr Sa Su
Jan |  Today  | Mar
      1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28               

Tags

Search this blog

Most recent comments

  • One thing that was glossed over is that if you use Spring, there is a filter you can put in your XML… by Mathew Mannion on this entry
  • You are my hero. by Mathew Mannion on this entry
  • And may all your chickens come home to roost – in a nice fluffy organic, non–supermarket farmed kind… by Julie Moreton on this entry
  • Good luck I hope that you enjoy the new job! by on this entry
  • Good luck Kieran. :) by on this entry

Galleries

Not signed in
Sign in

Powered by BlogBuilder
© MMXVII