3 Replies Latest reply on Jan 14, 2013 2:44 PM by Adam Warski

    Index on "Foreign Keys" of Real Tables in Envers

    Mark Torres Newbie

      We recently encountered some slow queries on our audit tables that is caused by statements similar to

       

      select e

      from package.Child_AUD e

      where e.parent_id = :parent_id and e.originalId.REV.id =

      (select max(e2.originalId.REV.id)

      from package.Child_AUD e2

      where e2.originalId.REV.id <= :revision and e.originalId.id = e2.originalId.id) and REVTYPE != :delrevisiontype

       

      We traced this query to the loading of OneToMany relationship between Parent and child.

       

      I'm aware of the ValidityAuditStrategy but decided against using it initially since we'd rather keep our saves faster.

       

      Im exploring the possibility of indexing the envers columns which corresponds to the FKs of the real table as an alternatice.

      In this example adding an index on Child_AUD.parent_id. This does improve the query plan on mysql from doing a full table scan to using the index.

       

      For now, we have some custom code that will generate these indices as part of the hbm2ddl process.

       

      What are your thoughts on this as an alternative to ValidityAuditStrategy?

      Are there scenarios where these indices will not produce an improvement? One scenario I can think of is if an entity changes a lot such that an index on e.parent_id will match a lot of records. Although on our use case, we dont have such an entity yet.

       

      If this is a viable solution, is there some plan on including it in a future release?

        • 1. Re: Index on "Foreign Keys" of Real Tables in Envers
          Adam Warski Master

          Sure, maybe an annotation to add an index where needed would be great. Plans on including depend really if somebody implements it

           

          The VAS allows to skip the subquery entirerly, so maybe it would have even better performance ... but that would require measuring, of course. Probablby depends on the DB as well.

           

          Adam

          • 2. Re: Index on "Foreign Keys" of Real Tables in Envers
            Mark Torres Newbie

            Adam,

             

            Instead of an annotation, how about adding index to all columns referred to by relationships that will be "loaded"? Maybe another configuration property can be added to control whether or not these indices are generated so other users can turn it off.

             

            VAS may indeed have better performance when querying, but with these indices, you dont get the extra SQL hit during DB writes. I agree that measuring will give us better insights on the actual differences.

             

            In our use case, it is preferable take the hit when pulling audit records, since this is not done frequently and only by a few users, rather than during commits.

             

            I do have some code that generates the indices but I dont think its in the best location. I wanted to code outside of the envers classes to minimize the impact during upgrades. Basically, I have a custom hibernate Configuration that intercepts calls to addDocument done by envers, and using AuditConfig metadata, I add the indexes on these Documents before passing it to the actual hibernate Configuration.

            • 3. Re: Index on "Foreign Keys" of Real Tables in Envers
              Adam Warski Master

              Property sounds good as well.

               

              Nice hack with the addDocument

               

              Adam