递归ORM类的Spring存储库性能问题 [英] 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存储库请求每个父元素(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] INFO 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 =? 休眠状态:选择技能0_.parent_id作为parent_i4_15_0_，技能0_.idSkill作为idSkill1_15_0_，技能0_.id技能作为idSkill1_15_1_，技能0_.levelBezeichnung_id作为levelBez3_15_1_，技能0_.id作为名称2_15_1_id_al_id_al_id_1_15__1_port_0_parent_id_port_0_parent_id是从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...

网络-2016-02-13 16:53:51,523 [http-nio-8080-exec-2]信息cscontroller.ProfileController--0:0:0:0:0:0:0:1-开始未检查发现 休眠状态:选择技能0_.parent_id作为parent_i4_15_0_，技能0_.idSkill作为idSkill1_15_0_，技能0_.id技能作为idSkill1_15_1_，技能0_.levelBezeichnung_id作为levelBez3_15_1_，技能0_.id作为名称2_15_1_id_al_id_al_id_1_15__1_port_0_parent_id_port_0_parent_id是从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] INFO 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

更新日志

网络-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=?

网络-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

网络-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

SKILLDTO:

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存储库性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！