我们有一个在GlassFish 4.1上运行的JavaEE Web应用程序。它在少量数据的情况下表现良好,但是现在数据变得越来越多。结果是像加载文档这样的简单请求大约要花1分钟的时间,因为它不必要地加载了几乎整个数据库。

这些是实体:

文件实体:

@Entity
@JsonIdentityInfo(generator=JSOGGenerator.class)
@NamedEntityGraph(
    name = "graph.Document.single",
    attributeNodes = {
        @NamedAttributeNode(value = "project", subgraph = "projectSubgraph")
    },
    subgraphs = {
        @NamedSubgraph(
            name = "projectSubgraph",
            attributeNodes = {
                @NamedAttributeNode("users")
            }
        )
    }
)
public class Document extends BaseEntity {

    @JsonView({ View.Documents.class, View.Projects.class })
    @Column(name = "Name")
    private String name;

    @JsonView({ })
    @JsonProperty(access = Access.WRITE_ONLY)
    @Column(name = "Text", columnDefinition = "TEXT")
    private String text;

    @JsonView({ View.Documents.class })
    @ManyToOne(cascade = { CascadeType.PERSIST, CascadeType.MERGE },
                optional = false)
    @JoinColumn(name = "project_fk")
    private Project project;

    @JsonView({ View.Documents.class, View.Projects.class })
    @OneToMany(cascade = { CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REMOVE },
                mappedBy = "document",
                fetch = FetchType.EAGER)
    private Set<State> states = new HashSet<>();

    @JsonView({ })
    @OneToMany(cascade = { CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REMOVE },
                fetch = FetchType.LAZY)
    @JoinTable(
        name="DOCUMENT_DEFAULTANNOTATIONS",
        joinColumns={@JoinColumn(name="DOC_ID", referencedColumnName="id")},
        inverseJoinColumns={@JoinColumn(name="DEFANNOTATION_ID", referencedColumnName="id")})
    private Set<Annotation> defaultAnnotations = new HashSet<>();

    ...
}


项目实体:

@Entity
@JsonIdentityInfo(generator=JSOGGenerator.class)
public class Project extends BaseEntity {

    @JsonView({ View.Projects.class })
    @Column(name = "Name", unique = true)
    private String name;

    @JsonView({ View.Projects.class })
    @OneToMany(mappedBy = "project",
                cascade = { CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REMOVE },
                fetch = FetchType.EAGER)
    private Set<Document> documents = new HashSet<>();

    @JsonView({ View.Projects.class })
    @ManyToMany(cascade = { CascadeType.PERSIST, CascadeType.MERGE }, fetch = FetchType.EAGER)
    @JoinTable(
        name="PROJECTS_MANAGER",
        joinColumns={@JoinColumn(name="PROJECT_ID", referencedColumnName="id")},
        inverseJoinColumns={@JoinColumn(name="MANAGER_ID", referencedColumnName="id")})
    private Set<Users> projectManager = new HashSet<>();

    @JsonView({ View.Projects.class })
    @ManyToMany(cascade = { CascadeType.PERSIST, CascadeType.MERGE }, fetch = FetchType.EAGER)
    @JoinTable(
        name="PROJECTS_WATCHINGUSERS",
        joinColumns={@JoinColumn(name="PROJECT_ID", referencedColumnName="id")},
        inverseJoinColumns={@JoinColumn(name="WATCHINGUSER_ID", referencedColumnName="id")})
    private Set<Users> watchingUsers = new HashSet<>();

    @JsonView({ View.Projects.class })
    @ManyToMany(mappedBy = "projects",
                cascade = { CascadeType.PERSIST, CascadeType.MERGE },
                fetch = FetchType.EAGER)
    private Set<Users> users = new HashSet<>();

    @JsonView({ View.Projects.class })
    @ManyToOne(cascade = { CascadeType.PERSIST, CascadeType.MERGE },
                fetch = FetchType.EAGER)
    @JoinColumn(name="Scheme", nullable = false)
    private Scheme scheme;

    ...
}


数据模型非常复杂,具有部分循环的结构。

相应的DocumentDAO:

@Stateless
@TransactionAttribute(TransactionAttributeType.MANDATORY)
public class DocumentDAO extends BaseEntityDAO<Document> {

    public DocumentDAO() {
        super(Document.class);
    }

    public Document getDocumentById(Long docId) {

        EntityGraph graph = this.em.getEntityGraph("graph.Document.single");

        TypedQuery query = em.createQuery("SELECT d.id AS id, d.name AS name, d.project AS project " +
                                    "FROM Document d " +
                                    "JOIN FETCH d.project " +
                                    "WHERE d.id = :id ", Document.class);
        query.setParameter("id", docId);
        //query.setHint("javax.persistence.loadgraph", graph);
        //query.setHint("javax.persistence.fetchgraph", graph); //evokes an exception
        Object[] result  = (Object[]) query.getSingleResult();

        Document doc = new Document();
        doc.setId((Long) result[0]);
        doc.setName((String) result[1]);
        doc.setProject((Project) result[2]);

        return doc;
    }

}


在简单的em.find(Document.class,docId)执行之前,它的运行速度也很慢。因此,下一个尝试是创建一个NamedEntityGraph来覆盖获取策略。传递图形作为提示(em.find(Document.class,docId,提示))没有任何改变。像在DocumentDAO中一样,编写JPQL查询的行为相同。仅将NamedEntityGraph分配为提示会引起“ org.eclipse.persistence.exceptions.QueryException.fetchGroupNotSupportOnReportQuery:无法在报表查询中设置获取组”。我启用了EclipseLink日志记录,并且可以看到该请求引发了大量不必要的SQL查询。

目的只是返回包含id,名称和相应项目对象的Document对象。项目对象应仅包含用户。我也想知道为什么NamedEntityGraph没有更改任何东西,还是我使用不正确?

我们使用EclipseLink 2.6.2和PostgreSQL。

更新:

日志片段:

[2016-06-05T17:50:27.875+0200] [glassfish 4.1] [FINE] [] [org.eclipse.persistence.session./file:/Users/timtoheus/NetBeansProjects/discanno/target/discanno-1.0/WEB-INF/classes/_DiscAnnoPU.sql] [tid: _ThreadID=31 _ThreadName=http-listener-1(3)] [timeMillis: 1465141827875] [levelValue: 500] [[
  SELECT t1.ID, t1.EndS, t1.NotSure, t1.StartS, t1.Text, t1.document_fk, t1.targetType_fk, t1.user_fk FROM DOCUMENT_DEFAULTANNOTATIONS t0, ANNOTATION t1 WHERE ((t0.DOC_ID = ?) AND (t1.ID = t0.DEFANNOTATION_ID))
    bind => [38]]]

[2016-06-05T17:50:27.877+0200] [glassfish 4.1] [FINE] [] [org.eclipse.persistence.session./file:/Users/timtoheus/NetBeansProjects/discanno/target/discanno-1.0/WEB-INF/classes/_DiscAnnoPU.sql] [tid: _ThreadID=31 _ThreadName=http-listener-1(3)] [timeMillis: 1465141827877] [levelValue: 500] [[
  SELECT t1.ID, t1.EndS, t1.NotSure, t1.StartS, t1.Text, t1.document_fk, t1.targetType_fk, t1.user_fk FROM DOCUMENT_DEFAULTANNOTATIONS t0, ANNOTATION t1 WHERE ((t0.DOC_ID = ?) AND (t1.ID = t0.DEFANNOTATION_ID))
    bind => [39]]]

...

[2016-06-05T17:50:27.771+0200] [glassfish 4.1] [FINE] [] [org.eclipse.persistence.session./file:/Users/timtoheus/NetBeansProjects/discanno/target/discanno-1.0/WEB-INF/classes/_DiscAnnoPU.sql] [tid: _ThreadID=31 _ThreadName=http-listener-1(3)] [timeMillis: 1465141827771] [levelValue: 500] [[
  SELECT t1.ID, t1.LABEL_LabelId FROM ANNOTATION_LABELMAP t0, LABELLABELSETMAP t1 WHERE ((t0.ANNOTATION_ID = ?) AND (t1.ID = t0.MAP_ID))
    bind => [53649]]]

[2016-06-05T17:50:27.773+0200] [glassfish 4.1] [FINE] [] [org.eclipse.persistence.session./file:/Users/timtoheus/NetBeansProjects/discanno/target/discanno-1.0/WEB-INF/classes/_DiscAnnoPU.sql] [tid: _ThreadID=31 _ThreadName=http-listener-1(3)] [timeMillis: 1465141827773] [levelValue: 500] [[
  SELECT t1.ID, t1.LABEL_LabelId FROM ANNOTATION_LABELMAP t0, LABELLABELSETMAP t1 WHERE ((t0.ANNOTATION_ID = ?) AND (t1.ID = t0.MAP_ID))
    bind => [53650]]]

...

[2016-06-05T17:56:50.881+0200] [glassfish 4.1] [FINE] [] [org.eclipse.persistence.session./file:/Users/timtoheus/NetBeansProjects/discanno/target/discanno-1.0/WEB-INF/classes/_DiscAnnoPU.sql] [tid: _ThreadID=30 _ThreadName=http-listener-1(2)] [timeMillis: 1465142210881] [levelValue: 500] [[
  SELECT t1.ID, t1.LABEL_LabelId FROM ANNOTATION_LABELMAP t0, LABELLABELSETMAP t1 WHERE ((t0.ANNOTATION_ID = ?) AND (t1.ID = t0.MAP_ID))
    bind => [44220]]]

[2016-06-05T17:56:50.886+0200] [glassfish 4.1] [FINE] [] [org.eclipse.persistence.session./file:/Users/timtoheus/NetBeansProjects/discanno/target/discanno-1.0/WEB-INF/classes/_DiscAnnoPU.sql] [tid: _ThreadID=30 _ThreadName=http-listener-1(2)] [timeMillis: 1465142210886] [levelValue: 500] [[
  SELECT t1.ID, t1.LABEL_LabelId FROM ANNOTATION_LABELMAP t0, LABELLABELSETMAP t1 WHERE ((t0.ANNOTATION_ID = ?) AND (t1.ID = t0.MAP_ID))
    bind => [44221]]]

...


查询总数约为100.000。日志记录引用了此请求不需要的其他一些实体。最终结果应该是大约500kb,而不是7.1mb。

Chrome控制台:
java - JPA查询优化-LMLPHP

最佳答案

我不知道您的数据,但这是我所想的-

您有以下渴望的协会

document -> project  (manyToOne is eager by default)
document -> states
project -> documents
project -> users
user -> ... (this is not shown in question, but there could be other eager associations)


在将文档加载到相应项目后,


提取所有项目文件
获取所有项目用户
获取所有文档状态
对于步骤3中的每个文档,获取文档状态
对于步骤2中的每个用户,将加载所有渴望的关联


你知道我要去哪里。我认为这是(n + 1)问题与过度使用急切加载的结合,即使您不需要它也是如此。

我会说“急切”的获取策略对于复杂的对象图不是理想的选择。我将使用JPQL中的“ join fetch”语句将大多数关联作为惰性和加载对象图。

10-07 19:05
查看更多