Too many recurring Database calls in for loop in POST request of REST API

Question

private Integer getLongestDuration(List<Project> project) {
        if (project== null || project.isEmpty())
            return 0;
        List<Integer> results = new ArrayList<>();
        for (Project m : project) {
                scanRoute(m, results, Integer.valueOf(0));
        }
        return Collections.max(results);
    }

 private void scanRoute(Project m, List<Integer> results, Integer time) {

        Integer maxDuration = repoCall.calculateDuration(m.getId());
        time += (maxDuration == null ? 0 : maxDuration);
        List<Project> output = findElements(m); //DB Call
        results.add(time);
        if (!output.isEmpty()) {
            for (Project successor : output) {
                scanRoute(successor, results, time);
            }
        }
    }

The problem is when too many elements scanRoute() takes too much time causing it to timeout as a response. I want to optimize this method so that there should be fewer DB round trips and the timeout problem can be solved.

Answer 1

A few ideas:

1. You might be able to speed things up by hitting the database with a collection of projectId's instead of one at a time
2. If you get all the projects up front you might avoid duplicates by storing in a set / map
3. You could use parallelStream() to do some of the work in parallel

eg:

private Integer getLongestDuration(List<Project> projects) {
   if (projects == null || projects.isEmpty()) {
      return 0;
   }
   
   // maintain a map to avoid duplicates
   Map<Integer, Project> projectMap = new LinkedHashMap<>(); 

   List<Project> current = projects;
   while (!current.isEmpty()) {
      // remove duplicates
      current = current.stream()
         .filter(p -> !projectMap.containsKey(p.getId())
         .collect(Collectors.toList());

      // add to map
      current.forEach(p -> projectMap.put(p.getId(), p));

      // get next level of child projects
      current = findElements(current);
   }

   return projectMap
      .values()
      .parallelStream() // perform work in parallel
      .mapToInt(project -> repoCall.calculateDuration(project.getId())
      .max()
      .orElse(0);
}

private List<Project> findElements(List<Project> projects) {
   if (projects.isEmpty()) {
      return Collections.emptyList();
   }
   List<String> projectIds = projects.stream().map(Project::getId).collect(Collectors.toList());
   String questions = String.repeat("?,", projects.size() - 1) + "?";
   String sql = "select * from project where parent_id in (" + questions + ")";
   // todo hit database using an 'in' clause instead of once per project
}