Calculating and storing precomputed average with Hibernate

Question

Say I have a set of products and each product has a set of reviews. Each review has a score. I will need the average of the scores much more often then I will need the actual reviews. Are there any best practices/clean ways of precomputing the average and storing it with hibernate.

I am currently thinking of adding review count and review sum columns to the product table and rigging the add review method to update the count and sum.

I would be very glad to hear of a cleaner way of doing this.

Answer 1

I would advise against keeping a sum column on your parent table. If two competing requests attempt to update the sum column, one will fail or you risk clobbering the sum with a stale read (unless the system doesn't have high traffic...).

The queries you require are quite simple and any decent db should give good performance for awhile (lazy psuedo SQL/HQL):

1) A particular avg review score for a product:

select sum(score)/count from reviews where product_id = ?

2) List of products and their avg review scores

select product_name, sum(r.score)/count(r.score)
from products p
join reviews r
group by product_name

For the first query, make sure you have some sort of index on product_id on the reviews table.

Using hibernate, you can use projection queries either through HQL or the Criteria objects. If that's not fast enough, then I would look into second level caching and query caching.

Turn on show_sql and TestTestTest to make sure hibernate is only hitting the database once, and that your cache is invalidated when new items are added/updated.