10 Replies Latest reply on Jun 28, 2011 4:30 AM by benpoole

    Very slow ManyToMany queries

    benpoole Newbie

      Hi,

       

      We have a couple of ManyToMany relationships on an Activity entitybeing audited with envers, querying activity revisions using theAuditQueryCreator can take over 10 seconds.

       

      This is one of the slow queries envers is performing:

      select assoc_acti0_.REV as col_0_0_, assoc_acti0_.activity_id as col_0_1_, assoc_acti0_.facility_id as col_0_2_, facility_a1_.id as col_1_0_, facility_a1_.REV as col_1_1_

       

      from assoc_activity_facility_AUD assoc_acti0_

      cross join facility_AUD facility_a1_

       

      where assoc_acti0_.facility_id=facility_a1_.id

      and assoc_acti0_.activity_id=1207

      and facility_a1_.REV=(

      select max(facility_a2_.REV)

      from facility_AUD facility_a2_

      where facility_a2_.REV<=59576

      and facility_a1_.id=facility_a2_.id

      )

      and assoc_acti0_.REV=(

      select max(assoc_acti3_.REV)

      from assoc_activity_facility_AUD assoc_acti3_

      where assoc_acti3_.REV<=59576

      and assoc_acti0_.activity_id=assoc_acti3_.activity_id

      and assoc_acti0_.facility_id=assoc_acti3_.facility_id

      )

      and assoc_acti0_.REVTYPE<>2

      and facility_a1_.REVTYPE<>2;

       

      This is the Activity entity:

      @Entity

      @Table(name = "activity")

      @Audited

      public class Activity implements Serializable {

          private static final long serialVersionUID = 1L;

       

          public static final int[] VALID_PRIORITIES = { 0, 1, 2, 3 };

       

          @Id

          @GeneratedValue(strategy = GenerationType.IDENTITY)

          @Basic(optional = false)

          @Column(name = "id", nullable = false)

          private Long id;

       

       

           @JoinTable(

                  name = "assoc_activity_facility",

                  joinColumns = { @JoinColumn(name = "activity_id", referencedColumnName = "id", nullable = false) },

                  inverseJoinColumns = { @JoinColumn(name = "facility_id", referencedColumnName = "id", nullable = false) })

          @ManyToMany

          private List<Facility> facilityList;

       

      ....

       

      }

       

      This is the Facility entity:

       

      @Entity

      @Table(name = "facility")

      @Audited

      public class Facility implements Serializable {

       

          private static final long serialVersionUID = 1L;

          @Id

          @GeneratedValue(strategy = GenerationType.IDENTITY)

          @Basic(optional = false)

          @Column(name = "id", nullable = false)

          private Long id;

       

          @ManyToMany(mappedBy = "facilityList")

          private List<Location> locationList;

       

          @ManyToMany(mappedBy = "facilityList")

          private List<Activity> activityList;

       

      ....

       

      }

       

      Is there a better way to implement these relationships to prevent the slowsubselects?

       

      Any help on this would be much appreciated.

       

      Thanks

       

      Ben

        • 1. Very slow ManyToMany queries
          Adam Warski Master

          Did you try using the ValidityAuditStrategy?

           

          Adam

          • 2. Very slow ManyToMany queries
            benpoole Newbie

            Hi Adam,

             

            Unfortunately we are using the default audit strategy and at this stage of the project regenerating the schema is not an option.

             

            I noticed your blog post about using the end-revision column, but again this requires the ValidityAuditStrategy.

             

            Temporarily we have disabled the many-to-many auditing to prevent the slow queries.

             

            Is there any other way to optimise the query? The Facility table doesn’t actually need auditing, only the association table. Is there a way to

            only audit the association table?

             

            Thanks

             

            Ben

            • 3. Very slow ManyToMany queries
              Adam Warski Master

              Maybe @Audited(targetEntityAuditMode=NOT_AUDITED) is what you are after?

               

              Adam

              • 4. Very slow ManyToMany queries
                benpoole Newbie

                Hi Adam,

                 

                I did try that annotation on the one-to-many relationship with no joy.

                 

                Now the site has launched and the database has been populated we are experiencing additional slow queries realted to sub-queries and have decided to implement the ValidityAuditStrategy for version 2 of the project.

                 

                Is it possible to switch Audit Strategy with an existing dataset?

                 

                Thanks again

                 

                Ben

                • 5. Very slow ManyToMany queries
                  Adam Warski Master

                  Sure, you just need to populate the addtional "revend" column. Several SQL queries should do the job.

                   

                  Adam

                  • 6. Re: Very slow ManyToMany queries
                    benpoole Newbie

                    Hi Adam,


                    We have now upgraded Envers and switched to the ValidityAuditStrategy giving a great improvement in performance, thanks for the advice.


                    We are however experiencing a slight issue with with the ManyToMany relationship explained above.


                    The issue is that when updating the Facilities for an Activity the REVEND column in the assoc_activity_facility_AUD is not being populated.

                    This happens with all ManyToMany relationships.

                    This causes the following error the next time an update is attempted:

                    Cannot find previous revision for entity assoc_activity_facility_AUD and id {REV=DefaultRevisionEntity(id = 99264, revisionDate = Jun 3, 2011 12:21:02 PM), Activity_id=2581, facilityList_id=2}
                    

                     


                    AUD tables before the update:


                    activity_AUD
                    idREVREVTYPEtitleREVEND
                    2581992661New activity Envers upgrade(NULL)
                    2581992651New activity Envers upgrade99266
                    2581992631New activity Envers upgrade99265
                    2581992621New activity Envers upgrade99263
                    2581992601New activity Envers upgrade99262

                     

                    facility_AUD
                    idREVREVTYPEtitleREVEND
                    2992661(NULL)(NULL)
                    2992651Toilets99266
                    2992631(NULL)99265
                    2992621Toilets99263
                    2992601(NULL)99262

                     

                    assoc_activity_facility_AUD
                    activity_idfacility_idREVREVTYPEREVEND
                    25812992660(NULL)
                    2581299265299266
                    2581299263099265
                    2581299262299263
                    2581299260099262
                    2581299259299260
                    2581299258099259

                     


                    AUD tables after the update:

                     


                    activity_AUD
                    idREVREVTYPEtitleREVEND
                    2581992681New activity Envers upgrade(NULL)
                    2581992671New activity Envers upgrade99268
                    2581992661New activity Envers upgrade(NULL)
                    2581992651New activity Envers upgrade99266
                    2581992631New activity Envers upgrade99265
                    2581992621New activity Envers upgrade99263
                    2581992601New activity Envers upgrade99262

                     

                    facility_AUD
                    idREVREVTYPEtitleREVEND
                    2992681(NULL)(NULL)
                    2992671Toilets99268
                    2992661(NULL)99267
                    2992651Toilets99266
                    2992631(NULL)99265
                    2992621Toilets99263
                    2992601(NULL)99262

                     

                    assoc_activity_facility_AUD
                    activity_idfacility_idREVREVTYPEREVEND
                    25812992680(NULL)
                    25812992672(NULL)
                    2581299266099267
                    2581299265299266
                    2581299263099265
                    2581299262299263
                    2581299260099262
                    2581299259299260
                    2581299258099259

                     

                    As you can see the REVEND column on assoc_activity_facility has not been updated.

                    Do I need an additional annotation so that the REVEND column in the association table gets populated along with the other tables?

                    I've read about @AuditMappedBy and @PositionMappedBy but was not sure whether these are relevant in this case.


                    Any help will be much appreciated.


                    Thanks again for you help so far.

                    • 7. Re: Very slow ManyToMany queries
                      benpoole Newbie

                      Update:

                       

                      I found a thread on the Hibernate forum where a user has the same issue, he states that this is a bug in Envers which won't be resolved until version 3.6.5.  https://forum.hibernate.org/viewtopic.php?f=1&t=1009550&p=2445759#p2445759

                       

                      Because of this we have written a workaround which involves overriding the collection update event listener:

                      <entry key="hibernate.ejb.event.pre-collection-remove" value="my.overriden.RevisioningAuditEventListener" />
                      

                       

                      For each ManyToMany relationship we run a variation of the following query:

                      UPDATE assoc_location_facility_AUD SET REVEND = (
                                      SELECT REV FROM (
                                                      SELECT REV, location_id
                                                      FROM assoc_location_facility_AUD 
                                                      WHERE location_id = 11
                                      ) x 
                                      WHERE x.location_id = assoc_location_facility_AUD.location_id 
                                      AND x.REV > assoc_location_facility_AUD.REV 
                                      ORDER BY REV ASC 
                                      LIMIT 1
                      )
                      WHERE location_id = 11 AND REVEND IS null
                      

                       

                      This ensures the REVEND column is populated following the collection update event, tested on 7 ManyToMany relationships so far with 100% a success rate. Can you see any problems with this workaround?

                       

                      Also, do you know when 3.6.5 will be released?

                       

                      Thanks

                       

                      Ben

                      • 8. Re: Very slow ManyToMany queries
                        Adam Warski Master

                        Yes, I remember fixing this issue recently. I'm not sure when 3.6.5 will be out, but I'd expect pretty soon. I'll let you know if I'll have any additional info.

                        In the meantime, maybe you could test with 3.6.5-SNAPSHOT to verify that this is indeed fixed?

                         

                        Adam

                        • 9. Re: Very slow ManyToMany queries
                          Adam Warski Master

                          In fact 3.6.5 got release today

                           

                          Adam

                          • 10. Re: Very slow ManyToMany queries
                            benpoole Newbie

                            Hi Adam,

                             

                            We are now using 3.6.5.Final and the issue has been resolved.

                             

                            Thanks again for your help.

                             

                            Ben