0 Replies Latest reply on Feb 7, 2009 3:09 PM by Onur AKTAS

    How to cache a Query like that?

    Onur AKTAS Newbie

      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.