递归 ORM 类的 Spring Repository 性能问题 [英] Spring Repository performance issues with recursive ORM Class

问题描述

我的目标:

我在列表中列出了最低级别的孩子(例如技能 ID 10 和 12).现在，我想要每个孩子(在本例中为父母 34)的所有父母(parent_id = null)并将它们再次保存在列表中.毕竟我想要从父母到每个孩子的路径(34-9-10 和 34-9-12).稍后我想检查这些路径(34、9、10、12)上的每个技能.

I have the children on the lowest level (for exmaple skill id 10 and 12) in a list. Now, I want all parents (parent_id = null) for every child (in this case, parent 34) and save them in a list again. After all I want the path from the parent to each child (34-9-10 and 34-9-12). Later on I want to check on every skill on these paths (34, 9, 10, 12).

最后，我有一个技能集合，说明了从上到下的路径.

Finally, I have a collection of skills that illustrates the pathes from top to bottom.

情况:

我正在使用 MariaDB(MySQL 方言)并有以下递归表(从 idSkill:9 到父级 34)

I am using MariaDB (MySQL Dialect) and have the following, recursive table (from idSkill: 9 to parent 34)

现在我要求使用 Spring Crud Repository 获取每个父元素 (parent_id = null).为此，我使用了一个循环，该循环遍历具有所有父元素 ID 的列表，并为每个父元素 ID 调用 findOne(parentelementid) 并使用 LAZY 加载:​​

Now I am asking for every parent element (parent_id = null) with Spring Crud Repository. To do so I am using a loop that is iterating over a list with all parent-element-ids and calling findOne(parentelementid) for every parent element id and using LAZY Loading:

List<Skill> parentList = skillDAO.findBySkill(null);
HashMap<Integer, ArrayList<Integer>> parentTree = customSkillDAO.findParentIdsByPersonSkills(listPersonskill);

    //Integer: Durchnummeriert zur Eindeutigkeit, von 0,1,2...
    //List: Pfad vom höchsten Vaterlement zum niedrigsten Personskill
    //Notwendig, um den Pfad pro niedrigsten Knoten auf true zu setzen
    HashMap<Integer, ArrayList<Integer>> parentTree = customSkillDAO.findParentIdsByPersonSkills(listPersonskill);
    log.info("START FINDING CHECKED");
//keySet is just numbered from 0,1,2,3...
for (int counter : parentTree.keySet()) {
        //parentTree.get(counter) gives a list whith Integer that describes the path from top to bottom.
        //So the first element is always the parent.
        mapParentSkills.put(parentTree.get(counter).get(0), new SkillDTO(skillDAO.findOne(parentTree.get(counter).get(0))));
        mapParentSkills.get(parentTree.get(counter).get(0)).setChecked(true);
    }
    log.info("START FINDING NOT CHECKED");
//Add all other parent that are not checked
for (Skill skill : parentList) {
        if (!mapParentSkills.containsKey(skill.getIdSkill())) {
            mapParentSkills.put(skill.getIdSkill(), new SkillDTO(skill));
        }
    }

    log.info("ENDE SKILLS");

我得到了整棵树，这很好.唯一的问题是它需要大约 10 秒.谁能告诉我一些建议来改进它以至少在 <2 秒内完成它?

I am getting the whole tree wich is fine. The only issue is that it take about 10 seconds. Can anyone tell me some suggestion to improve it to do it at least in <2 secs?

这是我的课:

public class Skill implements java.io.Serializable {

    public Skill() {
}


@Id
@GeneratedValue(strategy = IDENTITY)

@Column(name = "idSkill", unique = true, nullable = false)
public Integer getIdSkill() {
    return this.idSkill;
}

public void setIdSkill(Integer idSkill) {
    this.idSkill = idSkill;
}

...一些未加载的@JsonBackReferences

...Some @JsonBackReferences, which are not loaded

@JsonBackReference
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "parent_id")
public Skill getSkill() {
    return this.skill;
}

public void setSkill(Skill skill) {
    this.skill = skill;
}

@JsonManagedReference
@OneToMany(fetch = FetchType.LAZY, mappedBy = "skill")
public Set<Skill> getSkills() {
    return this.skills;
}

public void setSkills(Set<Skill> skills) {
    this.skills = skills;
}

}

日志:

web - 2016-02-13 16:53:50,163 [http-nio-8080-exec-2] 信息 cscontroller.ProfileController - - 0:0:0:0:0:0:0:1 - 开始检查结果休眠:从 quanto_portal.levelBezeichnung levelbezei0_ 中选择 levelbezei0_.idLevelBezeichnung 作为 idLevelB1_4_0_，levelbezei0_.bezeichnung 作为 bezeichn2_4_0_ 其中 levelbezei0_.idLevelBezeichnung=?休眠:选择skill0_.parent_id 作为parent_i4_15_0_，skill0_.idSkill 作为idSkill1_15_0_，skill0_.idSkill 作为idSkill1_15_1_，skills0_.levelBezeichnung_id 作为levelBez3_15_1_，skill0_.name 作为name2_15_1_，skill0_.parent_id 作为parent_i4_15_1_，skills0_.parent_id 作为parent_i4_15_1_，其中skills0_portskills.=skills.=0/p>

web - 2016-02-13 16:53:50,163 [http-nio-8080-exec-2] INFO c.s.controller.ProfileController - - 0:0:0:0:0:0:0:1 - START FINDING CHECKED Hibernate: select levelbezei0_.idLevelBezeichnung as idLevelB1_4_0_, levelbezei0_.bezeichnung as bezeichn2_4_0_ from quanto_portal.levelBezeichnung levelbezei0_ where levelbezei0_.idLevelBezeichnung=? Hibernate: select skills0_.parent_id as parent_i4_15_0_, skills0_.idSkill as idSkill1_15_0_, skills0_.idSkill as idSkill1_15_1_, skills0_.levelBezeichnung_id as levelBez3_15_1_, skills0_.name as name2_15_1_, skills0_.parent_id as parent_i4_15_1_ from quanto_portal.skill skills0_ where skills0_.parent_id=?

...相同的选择约 50 次...

...Same select for ~50 times...

web - 2016-02-13 16:53:51,523 [http-nio-8080-exec-2] 信息 cscontroller.ProfileController - - 0:0:0:0:0:0:0:1 - 开始发现未检查休眠:选择skill0_.parent_id 作为parent_i4_15_0_，skill0_.idSkill 作为idSkill1_15_0_，skill0_.idSkill 作为idSkill1_15_1_，skills0_.levelBezeichnung_id 作为levelBez3_15_1_，skill0_.name 作为name2_15_1_，skill0_.parent_id 作为parent_i4_15_1_，skills0_.parent_id 作为parent_i4_15_1_，其中skills0_portskills.=skills.=0/p>

web - 2016-02-13 16:53:51,523 [http-nio-8080-exec-2] INFO c.s.controller.ProfileController - - 0:0:0:0:0:0:0:1 - START FINDING NOT CHECKED Hibernate: select skills0_.parent_id as parent_i4_15_0_, skills0_.idSkill as idSkill1_15_0_, skills0_.idSkill as idSkill1_15_1_, skills0_.levelBezeichnung_id as levelBez3_15_1_, skills0_.name as name2_15_1_, skills0_.parent_id as parent_i4_15_1_ from quanto_portal.skill skills0_ where skills0_.parent_id=?

..相同的选择数百次...

..Same select several hundred times...

web - 2016-02-13 16:53:59,289 [http-nio-8080-exec-2] 信息 cscontroller.ProfileController - - 0:0:0:0:0:0:0:1 -ENDE技能

web - 2016-02-13 16:53:59,289 [http-nio-8080-exec-2] INFO c.s.controller.ProfileController - - 0:0:0:0:0:0:0:1 - ENDE SKILLS

更新日志

web - 2016-02-13 19:48:25,471 [http-nio-8080-exec-2] 信息 cscontroller.ProfileController - - 0:0:0:0:0:0:0:1 - 开始检查结果

web - 2016-02-13 19:48:25,471 [http-nio-8080-exec-2] INFO c.s.controller.ProfileController - - 0:0:0:0:0:0:0:1 - START FINDING CHECKED

休眠:从 quanto_portal.levelBezeichnung levelbezei0_ 中选择 levelbezei0_.idLevelBezeichnung 作为 idLevelB1_4_0_，levelbezei0_.bezeichnung 作为 bezeichn2_4_0_ 其中 levelbezei0_.idLevelBezeichnung=?

Hibernate: select levelbezei0_.idLevelBezeichnung as idLevelB1_4_0_, levelbezei0_.bezeichnung as bezeichn2_4_0_ from quanto_portal.levelBezeichnung levelbezei0_ where levelbezei0_.idLevelBezeichnung=?

web - 2016-02-13 19:48:25,806 [http-nio-8080-exec-2] 信息 cscontroller.ProfileController - - 0:0:0:0:0:0:0:1 - 开始发现未检查

web - 2016-02-13 19:48:25,806 [http-nio-8080-exec-2] INFO c.s.controller.ProfileController - - 0:0:0:0:0:0:0:1 - START FINDING NOT CHECKED

web - 2016-02-13 19:48:25,807 [http-nio-8080-exec-2] 信息 cscontroller.ProfileController - - 0:0:0:0:0:0:0:1 -ENDE技能

web - 2016-02-13 19:48:25,807 [http-nio-8080-exec-2] INFO c.s.controller.ProfileController - - 0:0:0:0:0:0:0:1 - ENDE SKILLS

技能:

public SkillDTO(Skill skill) {
    idSkill = skill.getIdSkill();
    name = skill.getName();
    levelBezeichnung = skill.getLevelBezeichnung().getBezeichnung();
    checked = skill.isChecked();
    if (skill.getSkills().size() > 0) {
        Iterator<Skill> iteratorSkill = skill.getSkills().iterator();
        while (iteratorSkill.hasNext()) {
            Skill tempSkill = iteratorSkill.next();
            skills.add(convertSkillsToProfileDTO(tempSkill));
        }
    }
}

private SkillDTO convertSkillsToProfileDTO(Skill skill) {
    return new SkillDTO(skill);
}

推荐答案

我最终在缓存中加载了技能，而没有重新设计我的表.

I ended up loading skills in cache without redesigning my tables.

见:Spring boot 从 bean 中的数据库预加载数据

这篇关于递归 ORM 类的 Spring Repository 性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！