These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 
Author Message
 Post subject: JPA : optimize query involving large ManyToMany join table
PostPosted: Tue Feb 09, 2010 6:21 am 
Newbie

Joined: Tue Feb 09, 2010 6:05 am
Posts: 1
Location: Paris, France
Hi all.
I'm using Hibernate Entity Manager 3.4.0.GA with Spring 2.5.6 and MySql 5.1.
I have a use case where an entity called Artifact has a reflexive many-to-many relation with itself, and the join table is quite large (1 million lines). As a result, the HQL query performed by one of the methods in my DAO takes a long time.
Any advice on how to optimize this and still use HQL ? Or do I have no choice but to switch to a native SQL query that would perform a join between the table ARTIFACT and the join table ARTIFACT_DEPENDENCIES ?

Here is the problematic query performed in the DAO :

Code:
@SuppressWarnings("unchecked")
   public List<Artifact> findDependentArtifacts(Artifact artifact) {
      Query query = em.createQuery("select a from Artifact a where :artifact in elements(a.dependencies)");
      query.setParameter("artifact", artifact);
      List<Artifact> list = query.getResultList();
      return list;
   }


And the code for the Artifact entity :

Code:
package com.acme.dependencytool.persistence.model;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.Table;
import javax.persistence.UniqueConstraint;

@Entity
@Table(name = "ARTIFACT", uniqueConstraints={@UniqueConstraint(columnNames={"GROUP_ID", "ARTIFACT_ID", "VERSION"})})
public class Artifact {
   
    @Id
    @GeneratedValue
    @Column(name = "ID")
    private Long id = null;
   
   @Column(name = "GROUP_ID", length = 255, nullable = false)
   private String groupId;
   
   @Column(name = "ARTIFACT_ID", length = 255, nullable = false)
   private String artifactId;
   
   @Column(name = "VERSION", length = 255, nullable = false)
   private String version;
   
   @ManyToMany(cascade=CascadeType.ALL, fetch=FetchType.EAGER)
   @JoinTable(
         name="ARTIFACT_DEPENDENCIES",
         joinColumns = @JoinColumn(name="ARTIFACT_ID", referencedColumnName="ID"),
         inverseJoinColumns = @JoinColumn(name="DEPENDENCY_ID", referencedColumnName="ID")
   )
   private List<Artifact> dependencies = new ArrayList<Artifact>();

   public Long getId() {
      return id;
   }

   public void setId(Long id) {
      this.id = id;
   }

   public String getGroupId() {
      return groupId;
   }

   public void setGroupId(String groupId) {
      this.groupId = groupId;
   }

   public String getArtifactId() {
      return artifactId;
   }

   public void setArtifactId(String artifactId) {
      this.artifactId = artifactId;
   }

   public String getVersion() {
      return version;
   }

   public void setVersion(String version) {
      this.version = version;
   }
   
   public List<Artifact> getDependencies() {
      return dependencies;
   }

   public void setDependencies(List<Artifact> dependencies) {
      this.dependencies = dependencies;
   }
}


Thanks in advance.
Fabien


Top
 Profile  
 
 Post subject: Re: JPA : optimize query involving large ManyToMany join table
PostPosted: Wed Feb 10, 2010 5:30 pm 
Regular
Regular

Joined: Mon Jan 05, 2009 6:42 pm
Posts: 99
Location: IL
Hi- So do you need Eager fetching? Also I think for this query its easier to do and may be better? with native SQL.
Also what query does hibernate generate for the HQL?

-Srilatha.


Top
 Profile  
 
 Post subject: Re: JPA : optimize query involving large ManyToMany join table
PostPosted: Thu Feb 11, 2010 4:19 am 
Expert
Expert

Joined: Tue Jun 16, 2009 3:36 am
Posts: 990
As first step you should get the sql-statement which i sent to the db.
For this I suggest to log the sql-statement using P6spy or maybe you can even trace the sql-statement
on the MySql database server.
Then the next step would be analyzing which execution plan MySql uses for this query.
Is there an index used in the execution plan or does it make table-scans? Maybe you need to create some additional index.
Try to change the query and rerun it in a query-tool like Squirrel measuring the time it takes.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.