java 查询带有 JOIN FETCH 性能问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4944985/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query with JOIN FETCH performance problem
提问by michal.kreuzman
I have problem with hibernate query performance which I can't figure out. In code snippet below I need select entities with at least one mapping and filtered mapping. I'm using FETCH JOIN for this to load only filtered mappings. But in that case I have performance problems with query. Hibernate says warning log :
我有无法弄清楚的休眠查询性能问题。在下面的代码片段中，我需要选择具有至少一个映射和过滤映射的实体。我为此使用 FETCH JOIN 仅加载过滤的映射。但在那种情况下，我有查询的性能问题。Hibernate 说警告日志：
org.hibernate.hql.ast.QueryTranslatorImpl - firstResult/maxResults specified with collection fetch; applying in memory!
org.hibernate.hql.ast.QueryTranslatorImpl - 使用集合获取指定的 firstResult/maxResults；在记忆中申请！
When I omit FETCH JOIN and left only JOIN query is nice fast. But in result I have all mappings loaded to entity which is not acceptable state for me. Is there a way to boost query performance? There are a lot rows in mapping table.
当我省略 FETCH JOIN 并只留下 JOIN 查询时，速度很快。但结果我将所有映射加载到实体，这对我来说是不可接受的状态。有没有办法提高查询性能？映射表中有很多行。
HQL query :
HQL 查询：
select distinct e from Entity
join fetch e.mappings as mapping
where e.deleted = 0 and e.mappings is not empty
and e = mapping.e and mapping.approval in (:approvals)
Entities :
实体：
@Entity
@Table(name="entity")
class Entity {
...
@OneToMany(mappedBy="entity", cascade=CascadeType.REMOVE, fetch=FetchType.LAZY)
@OrderBy("created")
private List<Mapping> mappings = new ArrayList<Mapping>();
...
}
@Entity
@Table(name="mapping")
class Mapping {
public static enum MappingApproval {
WAITING, // mapping is waiting for approval
APPROVED, // mapping was approved
DECLINED; // mapping was declined
}
...
@ManyToOne(fetch=FetchType.EAGER)
@JoinColumn(name="entity_id", nullable=false)
private Entity entity;
@Enumerated(EnumType.STRING)
@Column(name="approval", length=20)
private MappingApproval approval;
...
}
Thanks
谢谢
采纳答案by michal.kreuzman
after increasing memory for JVM things goes much better. After all I end with not using FETCH in queries.
为 JVM 增加内存后，事情变得更好了。毕竟，我最终没有在查询中使用 FETCH。
回答by maerch
From the JPA-Specifications
来自 JPA 规范
The effect of applying setMaxResults or setFirstResult to a query involving fetch joins over collections is undefined. (JPA "Enterprise JavaBeans 3.0, Final Release", Kapitel 3.6.1 Query Interface)
将 setMaxResults 或 setFirstResult 应用于涉及对集合的 fetch 连接的查询的效果是未定义的。（JPA“企业JavaBeans 3.0，最终版本”，Kapitel 3.6.1 查询接口）
Hibernate does the right thing, but executes a part of the query in memory, which is tremendously slower. In my case the difference is between 3-5 ms to 400-500 ms.
Hibernate 做正确的事情，但在内存中执行查询的一部分，这非常慢。在我的情况下，差异在 3-5 毫秒到 400-500 毫秒之间。
My solution was to implement the paging within the query itself. Works fast with the JOIN FETCH.
我的解决方案是在查询本身内实现分页。使用 JOIN FETCH 快速工作。
回答by Vlad Mihalcea
The reason is slow is because Hibernate executes the SQL query with no pagination at all and the restriction is done in memory.
之所以慢，是因为 Hibernate 执行 SQL 查询时根本没有分页，而且限制是在内存中完成的。
However, if the join has to scan and fetch 100k records, while you are interested in just 100 results, then 99.9% of the work being done by the Extractor and all the I/O done over networking is just waste.
但是，如果连接必须扫描和获取 100k 条记录，而您只对 100 个结果感兴趣，那么 Extractor 完成的 99.9% 的工作以及通过网络完成的所有 I/O 都只是浪费。
As I explained in this article, you can easily turn a JPQL query that uses both JOIN FETCH and pagination:
正如我在本文中所解释的，您可以轻松地转换同时使用 JOIN FETCH 和分页的 JPQL 查询：
List<Post> posts = entityManager.createQuery(
"select p " +
"from Post p " +
"left join fetch p.comments " +
"where p.title like :title " +
"order by p.id", Post.class)
.setParameter("title", titlePattern)
.setMaxResults(maxResults)
.getResultList();
into an SQL query that limits the result using DENSE_RANK by the parent identifier:
进入一个 SQL 查询，该查询通过父标识符使用 DENSE_RANK 限制结果：
@NamedNativeQuery(
name = "PostWithCommentByRank",
query =
"SELECT * " +
"FROM ( " +
" SELECT *, dense_rank() OVER (ORDER BY \"p.created_on\", \"p.id\") rank " +
" FROM ( " +
" SELECT p.id AS \"p.id\", " +
" p.created_on AS \"p.created_on\", " +
" p.title AS \"p.title\", " +
" pc.id as \"pc.id\", " +
" pc.created_on AS \"pc.created_on\", " +
" pc.review AS \"pc.review\", " +
" pc.post_id AS \"pc.post_id\" " +
" FROM post p " +
" LEFT JOIN post_comment pc ON p.id = pc.post_id " +
" WHERE p.title LIKE :titlePattern " +
" ORDER BY p.created_on " +
" ) p_pc " +
") p_pc_r " +
"WHERE p_pc_r.rank <= :rank ",
resultSetMapping = "PostWithCommentByRankMapping"
)
@SqlResultSetMapping(
name = "PostWithCommentByRankMapping",
entities = {
@EntityResult(
entityClass = Post.class,
fields = {
@FieldResult(name = "id", column = "p.id"),
@FieldResult(name = "createdOn", column = "p.created_on"),
@FieldResult(name = "title", column = "p.title"),
}
),
@EntityResult(
entityClass = PostComment.class,
fields = {
@FieldResult(name = "id", column = "pc.id"),
@FieldResult(name = "createdOn", column = "pc.created_on"),
@FieldResult(name = "review", column = "pc.review"),
@FieldResult(name = "post", column = "pc.post_id"),
}
)
}
)
The query can be executed like this:
查询可以这样执行：
List<Post> posts = entityManager
.createNamedQuery("PostWithCommentByRank")
.setParameter(
"titlePattern",
"High-Performance Java Persistence %"
)
.setParameter(
"rank",
5
)
.unwrap(NativeQuery.class)
.setResultTransformer(
new DistinctPostResultTransformer(entityManager)
)
.getResultList();
To transform the tabular result set back into an entity graph, you need a ResultTransformerwhich looks as follows:
要将表格结果集转换回实体图，您需要一个ResultTransformer如下所示的：
public class DistinctPostResultTransformer
extends BasicTransformerAdapter {
private final EntityManager entityManager;
public DistinctPostResultTransformer(
EntityManager entityManager) {
this.entityManager = entityManager;
}
@Override
public List transformList(
List list) {
Map<Serializable, Identifiable> identifiableMap =
new LinkedHashMap<>(list.size());
for (Object entityArray : list) {
if (Object[].class.isAssignableFrom(entityArray.getClass())) {
Post post = null;
PostComment comment = null;
Object[] tuples = (Object[]) entityArray;
for (Object tuple : tuples) {
if(tuple instanceof Identifiable) {
entityManager.detach(tuple);
if (tuple instanceof Post) {
post = (Post) tuple;
}
else if (tuple instanceof PostComment) {
comment = (PostComment) tuple;
}
else {
throw new UnsupportedOperationException(
"Tuple " + tuple.getClass() + " is not supported!"
);
}
}
}
if (post != null) {
if (!identifiableMap.containsKey(post.getId())) {
identifiableMap.put(post.getId(), post);
post.setComments(new ArrayList<>());
}
if (comment != null) {
post.addComment(comment);
}
}
}
}
return new ArrayList<>(identifiableMap.values());
}
}
That's it!
而已！
回答by Eric Lanoiselee
If you need a firstResult/maxResults with "fetch" you can split your query in 2 queries:
如果您需要带有“fetch”的 firstResult/maxResults，您可以将您的查询拆分为 2 个查询：
Query your entity ids with firstResult/maxResults but without the "fetch" on sub-tables:
select entity.id from entity (without fetch) where .... (with firstResult/maxResults)Query your entities with the "fetch" on the ids returned by your first query:
select entity from entity fetch ... where id in <previous ids>
使用 firstResult/maxResults 查询您的实体 ID，但不使用子表上的“提取”：
select entity.id from entity (without fetch) where .... (with firstResult/maxResults)使用第一个查询返回的 ID 上的“提取”查询您的实体：
select entity from entity fetch ... where id in <previous ids>
