[jboss-user] [JBoss Cache: Core Edition] - How to cache a Query like that?

[onur.aktas]

Hi,
I use Hibernate with JBoss Cache.

I have entities like below, All i want is to retrieve Last 20 messages posted by User's Friends.

  | UserEntity (Long id)
  | FriendEntity (Long id, User user, Friend friend)
  | MessageEntity(Long id, User owner, String message, Timestamp created);
  | 

Because of performance issues, i use NativeQuery with UNION, not IN. 
For a User who have 1000 friends, i generate a query like:

  | (select * from messages where owner = 1 order by created DESC LIMIT 20)
  | UNION ALL
  | (select * from messages where owner = 2 order by created DESC LIMIT 20)
  | UNION ALL
  | (select * from messages where owner = 3 order by created DESC LIMIT 20)
  | ...
  | 997 more
  | ...
  | ORDER BY CREATED DESC LIMIT 20

It gives me the result i want but it is the worst way i have ever used. How can i use cache in order to improve the performance of such a Query?

Thanks.

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4207959#4207959

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4207959