Avoid n+1 loading while fetching orderables

Description

Orderables currently associate a huge number of other entites with them - program orderables, dispensables, orderable children, etc. Some of those have even more entity references that need to be loaded.

Our queries are not constructed properly at the moment what makes Hibernate load those references 1 by 1. We should investigate how the query can be improved so that we load as much stuff as possible with a single query.

For reference, the part that needs to be improved is the retrieveOrderables method in OrderableRepositoryImpl:
https://github.com/OpenLMIS/openlmis-referencedata/blob/master/src/main/java/org/openlmis/referencedata/repository/custom/impl/OrderableRepositoryImpl.java#L264

Also all native queries here have the same problem: - we should get rid of as many native queries as possible.

A simple way to track SQL queries that are sent is to add

to the settings.env file.

 

Resources worth checking out:

 

Status

Assignee

Klaudia Pałkowska

Reporter

Sebastian Brudziński

Labels

Story Points

13

Time tracking

140h

Epic Link

Components

Fix versions

Priority

Critical
Configure