7 Replies Latest reply on Oct 20, 2010 9:15 AM by adamw

    Performance problem with envers

    nandina

      Hi,

       

      While loading an object graph of audited data using envers large number of queries are issued.

       

      Suppose there is a class A having a collection of class B and B is having a collection of class C.

      When revision instance of Class A is queried, envers is loading collection B and collection C using "extra-lazy" fetch.

       

      Let's say if instance of A has 100 instances of B and each instance of B has 10 instances of C, then the total number of queries is:

      1 (load A) + 1 (load primary keys of collection B) + 100 (load instance of B) + 100 (load primary keys of collection C) + 1000 (load instances of collection C) = 1202 queries.

      These many number of queries are killing and the resonse time is in the order of minutes.

       

      Is there a way to force envers to use "lazy" fetch instead of "extra-lazy" to overcome the performance issue?

       

      Thanks in Advance,

       

      Srinivas Nandina

        • 1. Re: Performance problem with envers
          adamw

          I don't quite understand the difference between a lazy and an extra-lazy fetch?

           

          Adam

          • 2. Re: Performance problem with envers
            hernanbolido

            Hi!

             

            For collections:

             

            lazy -> the whole collection is selected when is accessed

            extra-lazy -> one select per element, it's accessing fetching collection element by element (this is envers case)

             

            Regards. Hernán.

            • 3. Re: Performance problem with envers
              nandina

              Hi Adam,

               

              I apologize for not making myself clear.

              I was referring to "lazy collection fetching" and "extra lazy collection fetching" in section 19.1 from this below page.

              http://docs.jboss.org/hibernate/core/3.3/reference/en/html/performance.html

               

              Thanks,

              Srinivas

              • 4. Re: Performance problem with envers
                nandina

                After looking deep into code I narrowed down the issue to syntax of HQL generated by envers.

                 

                Suppose I have an entity Person having 5 records, then the loading varies based on the syntax of HQL and org.hibernate.Query API.

                 

                1. session.createQuery("from Person p").list() --> results in 1 query.
                2. session.createQuery("from Person p").iterate() --> results in 6 queries.
                3. session.createQuery("select p from Person p").list() --> results in 1 query.
                4. session.createQuery("select new list(p) from Person p").list() --> results in 6 queries.

                 

                Envers is using option #4.

                Option #3 and Option #4 have both its own advantages and disadvantages.

                It would be great if this can be made as an configurable option either at class or at global level.

                 

                 

                Thanks,

                Srinivas

                • 5. Re: Performance problem with envers
                  adamw

                  Heh, always something new about Hibernate .

                  Does the extra-lazy fetching happen only when retrieving an object using a query, or also when retrieving it using find(Class, Number)?

                   

                  Anyway, this certainly needs to be fixed. Must have gotten broken when changing other things (I'm sure that relations where retrieved using 1 query when I originally wrote it). And it's a pretty hard thing to unit-test - maybe with hibernate statistics?

                   

                  Please file a JIRA and if you would be willing to try and approach the problem to create a patch, it would be great.

                   

                  Adam

                  • 6. Re: Performance problem with envers
                    nandina

                    Hi Adam,

                     

                    Thanks for response.

                    One thing that I want to mention is extra-lazy fetching option is also varying on the database.

                    For H2 database "lazy collection fetching" is used whereas for MySql "extra lazy collection fetching" is used.

                    This makes it much harder to test, if unit tests cannot be run on an in-memory database.

                     

                    Regarding find(Class, Number), to my knowledge it can only used to be fetch a single entity but not a collection.

                    Am I missing something here?

                     

                    I will file a JIRA for this.

                    I am trying to modify the code for fixing this issue but might need some help.

                     

                    Thanks,

                    Srinivas

                    • 7. Re: Performance problem with envers
                      adamw

                      Ah, I tested mainly on H2, hence I didn't notice the problems.

                       

                      You're right that find() really uses a query internally, so that was a stupid question . Nevermind.

                       

                      If you need any help ping me


                      Adam