[SOLVED] Spring Repository performance issues with recursive ORM Class

My aim:

I have the children on the lowest level (for example 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 paths from top to bottom.

Situation:

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

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 with 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");

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

Here is my class:

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;
}

...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;
}

}

LOG:

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

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

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 c.s.controller.ProfileController - - 0:0:0:0:0:0:0:1 - ENDE SKILLS

UPDATED LOG

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

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] 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] 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);
}