JPA 2 - How to cache/optimize count queries?

I have Java EE application, where I have list of groups. I want to display number of items in every group, so I do:

SELECT COUNT(p) FROM Product p WHERE LOWER(p.group) = LOWER(:group)

Group is plain String.

Problem is when I have 50+ groups, time to load page is about 10s, which is unacceptable. When I remove counting time drops to ~1s. How can I optimize/cache this?

My first idea was to use EJB singleton to keep counts locally and refresh them with EJB timer.

I'm using glassfish v3, eclipselink and oracle 10g database on same machine. I'm using cache in eclipselink, but it seems not to cache count queries.

<property name="eclipselink.cache.shared.default" value="false"/>
<property name="eclipselink.cach开发者_JAVA技巧e.size.default" value="500"/>

---

How about selecting all groups and associated counts in one query? This is not a JPA answer, but a plain SQL one:

SELECT LOWER(p.group), COUNT(*) 
FROM Product p
GROUP BY LOWER(p.group)

---

A long query is usually the sign of a missing index in the database. Try adding an index on product(lower(group)).

Also, it's hard to say withount knowing the excact use-case, but I find it strange to get the number of products of a group using a group label in the product table. Shouldn't you have a Group entity, and reference the group's primary key in the product table? This would eliminate the need of an index on lower(group) (but would need an index on product.group_id).

---

If you are passing in :group as parameter anyway, I would delegate the lowering part into java code.

SELECT COUNT(p) FROM Product p WHERE LOWER(p.group) = (:groupInLower)

This might have been optimized out by the SQL interpreter anyway, but it's worth a try...