Very slow ManyToMany queries
benpoole Mar 31, 2011 10:26 AMHi,
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")
@Auditedpublic 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