I am trying to optimize Hibernate queries for my data model. It is really basic data model for ARTICLE that contains a @CollectionOfElements ARTICLE_FACETS but when I query on articles EVERY QUERY takes > 30sec to complete.
Basically this code worked fine in the beginning, but as our db grew in size these selects became very heavy and unresponsive. Currently our ARTICLES table has ~100k rows and our ARTICLES_FACETS table has ~300k rows.
Basically we are under a requirement that queries take no longer than 300ms to complete. Is the solution reverse indexing from tags to article list? Should I add bi-directionality to the ARTICLE_FACETS table? How would I accomplish such a thing with Hibernate? Help please!
Code:
@Entity
@Table(name = "ARTICLES")
@Indexed
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class Article implements IArticle {
private static final long serialVersionUID = 1L;
private static Logger logger = LoggerFactory.getLogger(Article.class);
private Long id;
private String title = "";
private Date date;
private String desc = "";
private String text = "";
private String url = "";
private boolean goldStandard;
private List<String> facets = new ArrayList<String>();
public Article() {
}
public Article(Long id) {
this.id = id;
}
@Id
@Column(name = "ARTICLE_ID")
@GeneratedValue
public Long getId() {
return id;
}
@SuppressWarnings("unused")
private void setId(Long id) {
this.id = id;
}
@Column(name = "ARTICLE_TITLE")
@Type(type = "string")
@Field(index = Index.TOKENIZED, store = Store.NO)
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
@Column(name = "ARTICLE_DATE")
@Type(type = "timestamp")
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
@Column(name = "ARTICLE_DESC")
@Type(type = "text")
@Field(index = Index.TOKENIZED, store = Store.NO)
public String getDesc() {
return desc;
}
public void setDesc(String desc) {
this.desc = desc;
}
@Column(name = "ARTICLE_TEXT")
@Type(type = "text")
@Field(index = Index.TOKENIZED, store = Store.NO)
public String getText() {
return text;
}
public void setText(String text) {
this.text = text;
}
@Column(name = "ARTICLE_URL")
@Type(type = "string")
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
@Column(name = "ARTICLE_GOLD_STANDARD")
@Type(type = "boolean")
public boolean isGoldStandard() {
return goldStandard;
}
public void setGoldStandard(boolean goldStandard) {
this.goldStandard = goldStandard;
}
@org.hibernate.annotations.CollectionOfElements(fetch = FetchType.EAGER, targetElement = java.lang.String.class)
@JoinTable(name = "ARTICLE_FACETS", joinColumns = @JoinColumn(name = "ARTICLE_ID"))
@Column(name = "FACET_URI")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public List<String> getFacets() {
return facets;
}
public void setFacets(List<String> facets) {
this.facets = facets;
}
}
The HQL query that I issue.
Code:
select article from Article as article where 'http://www.somecompany.com/topics#War' in elements (article.facets)
With 'hibernate.show_sql=true' I see.
Code:
select
article0_.ARTICLE_ID as ARTICLE1_44_,
article0_.ARTICLE_DATE as ARTICLE2_44_,
article0_.ARTICLE_DESC as ARTICLE3_44_,
article0_.ARTICLE_GOLD_STANDARD as ARTICLE4_44_,
article0_.ARTICLE_TEXT as ARTICLE5_44_,
article0_.ARTICLE_TITLE as ARTICLE6_44_,
article0_.ARTICLE_URL as ARTICLE7_44_
from
ARTICLES article0_
where
(
'http://www.somecompany.com/topics#War' in (
select
facets1_.FACET_URI
from
ARTICLE_FACETS facets1_
where
article0_.ARTICLE_ID=facets1_.ARTICLE_ID
)
)
and article0_.ARTICLE_GOLD_STANDARD=1
Occasionally (not for every query) I see a plethora of additional selects that are all exactly the same. I'm not sure where these are coming from. (Could this be n+1 selects problem?) An example of one such select.
Code:
select
facets0_.ARTICLE_ID as ARTICLE1_0_,
facets0_.FACET_URI as FACET2_0_
from
ARTICLE_FACETS facets0_
where
facets0_.ARTICLE_ID=?
Hibernate Versionhibernate-core-3.3.0.SP1.jar
hibernate-commons-annotations-3.1.0.GA.jar
hibernate-annotations-3.4.0.GA.jar
hibernate-entitymanager-3.4.0.GA.jar
hibernate-search-3.1.0.GA.jar
UPDATEAs a note the ARTICLE_TEXT column contains data that is usually between 5-50k. I removed this column from the database and it seemed to speed up all queries. Is there a better way to handle the text of the article like putting it in its own entity separate from the rest of the article?
I hand rolled what I thought would be a better select than the one Hibernate generated. I'm not a DBA and so don't understand whether this query is ACTUALLY better, but it seems to run faster. Is the solution to hand roll my SQL and pass them to Hibernate? How would I tell Hibernate to build the entities based on my SQL?
Code:
select * from article_facets left join articles on article_facets.article_id = articles.article_id where facet_uri='http://www.somecompany.com/topics#War'
Thanks again!
Login
FAQ
Search