Ideas to bypass the sub-select query MAX for revisions?
I changed the envers-console-demo to show the generated SQL and ran it again.
Initial Person data:
id = 1, name = James, surname = Bond, address = <1> MI6 18/25 id = 2, name = John, surname = McClane, address = <2> Nakatomi Plaza 10/34 id = 3, name = Holly, surname = Gennaro, address = <2> Nakatomi Plaza 10/34
So Mr. Bond is with MI6, I go ahead and edit the address 3 times.
Address History:
revision = 1, id = 1, streetName = MI6, houseNumber = 18, flatNumber = 25, persons = (<1> James Bond) (Thu Jul 23 15:13:22 EDT 2009) revision = 2, id = 1, streetName = 14th Street, houseNumber = 18, flatNumber = 25, persons = (<1> James Bond) (Thu Jul 23 15:14:41 EDT 2009) revision = 3, id = 1, streetName = 15th Street, houseNumber = 18, flatNumber = 25, persons = (<1> James Bond) (Thu Jul 23 15:14:50 EDT 2009) revision = 4, id = 1, streetName = 16th Street, houseNumber = 18, flatNumber = 25, persons = (<1> James Bond) (Thu Jul 23 15:15:05 EDT 2009)
List Persons:
id = 1, name = James, surname = Bond, address = <1> 16th Street 18/25 id = 2, name = John, surname = McClane, address = <2> Nakatomi Plaza 10/34 id = 3, name = Holly, surname = Gennaro, address = <2> Nakatomi Plaza 10/34
When I search for History of Mr. Bond, I get this; The address corresponding to Mr. Bond at that revision is still MI6.
revision = 1, id = 1, name = James, surname = Bond, address = <1> MI6 18/25 (Thu Jul 23 15:13:22 EDT 2009)
I make John stay at the same address as Mr. Bond
revision = 1, id = 1, streetName = MI6, houseNumber = 18, flatNumber = 25, persons = (<1> James Bond) (Thu Jul 23 15:13:22 EDT 2009) revision = 2, id = 1, streetName = 14th Street, houseNumber = 18, flatNumber = 25, persons = (<1> James Bond) (Thu Jul 23 15:14:41 EDT 2009) revision = 3, id = 1, streetName = 15th Street, houseNumber = 18, flatNumber = 25, persons = (<1> James Bond) (Thu Jul 23 15:14:50 EDT 2009) revision = 4, id = 1, streetName = 16th Street, houseNumber = 18, flatNumber = 25, persons = (<1> James Bond) (Thu Jul 23 15:15:05 EDT 2009) revision = 6, id = 1, streetName = 16th Street, houseNumber = 18, flatNumber = 25, persons = (<2> John McClane, <1> Thames Bond) (Thu Jul 23 15:30:26 EDT 2009)
Went ahead and searched for the Address at that revision, for which I provide the following info;
----------------------------------------------- 1 - list persons 5 - list addresses 2 - list person history 6 - list addresses history 3 - new person 7 - new address 4 - modify person 8 - modify address 9 - get person at revision 10 - get address at revision 0 - end 10 Address id: 1 Revision number: 4 id = 1, streetName = 16th Street, houseNumber = 18, flatNumber = 25, persons = (<1> James Bond)
and I get this SQL generated.
Hibernate: -----------> {A}
select
address_ve0_.id as id3_,
address_ve0_._revision as column2_3_,
address_ve0_._revision_type as column3_3_,
address_ve0_.flatNumber as flatNumber3_,
address_ve0_.houseNumber as houseNum5_3_,
address_ve0_.streetName as streetName3_
from
Address_versions address_ve0_
where
address_ve0_._revision_type<>?
and (
address_ve0_._revision in (
select
max(address_ve1_._revision)
from
Address_versions address_ve1_
where
address_ve1_._revision<=?
and address_ve0_.id=address_ve1_.id
)
)
and address_ve0_.id=?
Hibernate: -----------> {B}
select
person_ver0_.id as col_0_0_,
person_ver0_._revision as col_0_1_
from
Person_versions person_ver0_
where
person_ver0_.address_id=?
and (
person_ver0_._revision in (
select
max(person_ver1_._revision)
from
Person_versions person_ver1_
where
person_ver1_._revision<=?
and person_ver0_.id=person_ver1_.id
)
)
and person_ver0_._revision_type<>?I can understand the {B} part of the code, where we need to find the Person using the address at that version, but for {A}, since we know the "exact" version of the address, can we avoid the subselect
and ( address_ve0_._revision in ( select max(address_ve1_._revision) from Address_versions address_ve1_ where address_ve1_._revision<=? and address_ve0_.id=address_ve1_.id )
Will be a nice performance improvement.
