3 Replies Latest reply on Jul 24, 2009 5:15 PM by Adam Warski

    Ideas to bypass the sub-select query MAX for revisions?

    Varun Mehta Newbie

      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.