I am executing a query involving a large IN clause between two entities in a one-to-one relationship. When I execute the query in SQL the way I would do it, it runs in less than half a second. When I do it through JPA-QL with Hibernate, the length of time increases by a factor of 10. I would love some guidance as to how I can eliminate this gap.
Here is the first entity called Document:
Code:
@Entity
@Table(name = "DOC")
public class Document {
...
@OneToOne(fetch = FetchType.EAGER) //Note: Lazy is not an option for me
@JoinColumn(name = "ARCHIVE_ID")
public DocumentArchive getDocumentArchive() {
return documentArchive;
}
public void setDocumentArchive(DocumentArchive documentArchive) {
this.documentArchive = documentArchive;
}
}
Here is the second entity called DocumentArchive:
Code:
@Entity
@Table(name = "DOC_ARCHIVE")
public class Archive {
...
@OneToOne(mappedBy = "documentArchive")
public Document getDocument() {
return document;
}
public void setDocument(Document document) {
this.document= document;
}
}
Finally, here is the JPA-QL query:
Code:
select new com.myapp.Document(doc.id, doc.subject, doc.documentArchive) from Document doc where doc.documentArchive.messageId in (:messageIds)
Note that the length of the messageIds collection could be as high as 1000.
I would love some advice on how to optimize this without resorting to a sql-result-set-mapping.
Thanks.