springboot/hibernate queries result takes too much time

Question

I am developing a Springboot application, and my requirements are the following:

I have 8 sql queries, that I have to run in parallel and that return a single result. My entities using this sql requests are:

@SqlResultSetMapping(name = "bud01Mapping", classes = {
        @ConstructorResult(targetClass = Bud01Entity.class, columns = {
                @ColumnResult(name = "MNTBUDGETMB", type = Double.class),

        }
        ) })

@Entity
@Getter
@Setter
@AllArgsConstructor
public class Bud01Entity {

    @Id
    private Double mntBudgetMB;

}

To avoid duplication in my post, please consider: Bud01Entity, Bud02Entity ... until Bud08Entity.

My queries are implemented in an hbm.xml file Bud01Entity.hbm.xml as follow:

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings
        xmlns="http://java.sun.com/xml/ns/persistence/orm"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_2_0.xsd"
        version="2.0">
    <entity
            class="budget.Bud01Entity"
            name="Bud01Entity">

        <named-native-query
                name="Bud01Entity.findBud01"
                result-set-mapping="bud01Mapping">
            <query>
                SELECT
                SUM(CASE WHEN budget.sens='C' THEN -1 * budget.mnt_budget ELSE budget.mnt_budget END) AS mntBudgetProduction,
                ent.code as codService
                FROM si.t_sb_budget budget
                JOIN si.t_sb_rubrique_budget rub ON rub.pk_id_rubrique_budget = budget.fk_id_rubrique_budget
                JOIN si.t_ga_activite act ON act.pk_id_activite = budget.fk_id_activite
                JOIN si.t_rd_service serv ON act.fk_id_service = serv.pk_id
                JOIN si.t_rd_entite ent ON ent.pk_id = serv.pk_id
                JOIN si.t_sb_type_budget typ_bud ON typ_bud.pk_id_type_budget=budget.fk_id_type_budget
                WHERE budget.exercice       = :codAnnee
                AND ent.code                = :codService
                AND typ_bud.pk_id_type_budget = 6
                GROUP BY
                ent.code
            </query>
        </named-native-query>

    </entity>
</entity-mappings>

As for the first one, to avoid duplication in my post, please consider: Bud01Entity.hbm.xml, Bud02Entity.hbm.xml ... Bud08Entity.hbm.xml.

My service is implemented as follow:

@Async
@Slf4j
@Service
public class Bud01Service {

    @Autowired
    private Bud01Repository bud01Repository;

    public CompletableFuture<List<Bud01Entity>> findBud(String codService, String codAnnee) {
        log.info("Running findBud for service {} and annee {}", codService, codAnnee);
        List<Bud01Entity> data = bud01Repository.findBud01(codService, codAnnee);
        return CompletableFuture.completedFuture(data);
    }
}

Same as the other, same logic for Bud02Service, ...Bud08Service.

My repository interface is as follow:

@Repository
public interface Bud01Repository extends JpaRepository<Bud01Entity, Double> {
    List<Bud01Entity> findBud01(@Param("codService") String codService, @Param("codAnnee") String codAnnee);
}

Each of this 8 results represent attributes of BudAllEntity:

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class BudAllEntity {

    private Double mntBudgetProduction;
    private Double mntBudgetMB;
    private Double mntBudgetRegul;
    private Double mntBudgetFraisGenAct;
    private Double mntBudgetFraisGenSer;
    private Double mntBudgetFraisGenReg;
    private Double mntBudgetFraisGenSie;
    private Double mntBudgetFraisFin;

}

And my aggregation endpoint:

@Slf4j
@RestController
@RequestMapping("/AggBud")
public class BudAggEndpoint {

    @Autowired
    private Bud01Service bud01Service;

    @Autowired
    private Bud02Service bud02Service;

    @Autowired
    private Bud03Service bud03Service;

    @Autowired
    private Bud04Service bud04Service;

    @Autowired
    private Bud05Service bud05Service;

    @Autowired
    private Bud06Service bud06Service;

    @Autowired
    private Bud07Service bud07Service;

    @Autowired
    private Bud08Service bud08Service;

    @GetMapping(value = "details", produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
    public Page<BudAllEntity> findBudClient(@RequestParam(value = "codService") String codService,
                                            @RequestParam(value = "codAnnee") String codAnnee,
                                            @RequestParam(required = false) Pageable pageRequest) throws ExecutionException, InterruptedException {

        CompletableFuture<List<Bud01Entity>> att1 = bud01Service.findBud(codService, codAnnee);
        CompletableFuture<List<Bud02Entity>> att2 = bud02Service.findBud(codService, codAnnee);
        CompletableFuture<List<Bud03Entity>> att3 = bud03Service.findBud(codService, codAnnee);
        CompletableFuture<List<Bud04Entity>> att4 = bud04Service.findBud(codService, codAnnee);
        CompletableFuture<List<Bud05Entity>> att5 = bud05Service.findBud(codService, codAnnee);
        CompletableFuture<List<Bud06Entity>> att6 = bud06Service.findBud(codService, codAnnee);
        CompletableFuture<List<Bud07Entity>> att7 = bud07Service.findBud(codService, codAnnee);
        CompletableFuture<List<Bud08Entity>> att8 = bud08Service.findBud(codService, codAnnee);

        CompletableFuture.allOf(att1, att2, att3, att4, att5, att6, att7, att8);

        log.info("Retrieve Budgets");

        List<BudAllEntity> finalResult = new ArrayList<BudAllEntity>();
        BudAllEntity result = new BudAllEntity();
        try{
            result.setMntBudgetProduction(att1.get().get(0).getMntBudgetProduction());
            result.setMntBudgetMB(att2.get().get(0).getMntBudgetMB());
            result.setMntBudgetRegul(att3.get().get(0).getMntBudgetRegul());
            result.setMntBudgetFraisGenAct(att4.get().get(0).getMntBudgetFraisGenAct());
            result.setMntBudgetFraisGenReg(att5.get().get(0).getMntBudgetFraisGenSer());
            result.setMntBudgetFraisGenSer(att6.get().get(0).getMntBudgetFraisGenReg());
            result.setMntBudgetFraisGenSie(att7.get().get(0).getMntBudgetFraisGenSie());
            result.setMntBudgetFraisFin(att8.get().get(0).getMntBudgetFraisFin());
        } catch (Exception e){
            log.error(e.getMessage(),e);
        }


        finalResult.add(result);
        Pageable page = Optional.ofNullable(pageRequest).orElse(PageRequest.of(0, 20));
        return new ResponsePage<>(finalResult, page, finalResult.size());
    }
}

I have added @EnableAsync annotation in my main class.

Is there a way to make this code more performant, more simple ?

Thanks in advance for your help

Answer 1

The approach you are following looks pretty good, but there are some minute mistakes you need to take care

@Async Annotation It should be annotated on method not class

• it must be applied to public methods only
• self-invocation – calling the async method from within the same class – won't work

The reasons are simple – the method needs to be public so that it can be proxied. And self-invocation doesn't work because it bypasses the proxy and calls the underlying method directly.

Override the Executor :

By default, Spring uses a SimpleAsyncTaskExecutor to actually run these methods asynchronously.

Override the default executor

@Configuration
@EnableAsync
public class SpringAsyncConfig {
 
    @Bean(name = "threadPoolTaskExecutor")
     public Executor threadPoolTaskExecutor() {
     ThreadPoolTaskExecutor pool = new ThreadPoolTaskExecutor();
     pool.setMaxPoolSize(10);
     return pool;
     }
 }

Then the executor name should be provided as an attribute in @Async:

@Async("threadPoolTaskExecutor")
public void asyncMethodWithConfiguredExecutor() {
    System.out.println("Execute method with configured executor - "
      + Thread.currentThread().getName());
}

Note : Make sure you create executor thread pool with N of threads that matches to cpu resources here