query to traverse associated entity slow?

[eitansuez]

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

[adamw]

Hello,

you're right, an uncorrelated subquery is much better. A way of tweaking envers is to create a patch . I'll be happy to commit it, if you provide create one .

Adam

[eitansuez]

hello,

  i thought it might be more

  efficient to communicate the issue to the envers

  team and have someone who already knows the codebase

  figure out how best revise the code.

  but i suppose i could take a look at the envers codebase and

  try to figure it out and patch it.  that would be most efficient for

  the person not doing the work.  :-)

  thanks for the speedy reply.

/ eitan