2 Replies Latest reply on May 11, 2010 11:23 AM by Eitan Suez

    query to traverse associated entity slow?

    Eitan Suez Newbie
      hello,
        i'm looking at a situation where a versioned entity, A, is retrieved.
       
        A a = AuditReader.find(A.class, id, versionId);
       
        this entity has an association to some other versioned entity, B.
        i noticed that when i traverse the association:
         a.getB().getId()
        
        the sql that is issued to the db looks like this:
       
        select b.* from b_version b
      where b.change_type<>2
        and b.version_id = (
          select max(b2.version_id)
          from b_version b2
          where b2.version_id<=${someversion}
           and b2.id=b.id
        )
        and b.id=${someid}
        the problem i have is that the above query is taking ~ 4 seconds.
        if i'm not mistaken, the following query is equivalent, and takes no time:
        select b.* from b_version b
      where b.change_type<>2
        and b.version_id = (
          select max(b2.version_id)
          from b_version b2
          where b2.version_id<=${someversion}
           and b2.id=${someid}
        )
        and b.id=${someid}
        can i tweak envers to produce the latter, more efficient query?  (or am i mistaken?)
       
      thanks,
      / eitan