Optimize Queries

[shresthaujjwal]

Overview of the issue

Need ways to optimize query so that there is a single inner join query for all 1-many join tables, currently by design if you have too many 1-many tables and if you use default jhipster built-in specification criteria it does lot of select to get the end result instead of doing just single 1 inner join to all the tables to get the result back.

Motivation for or Use Case

If you have to many select query instead of just 1 select query with inner join you will save the network cost when you are working with application with cross region traffic where database is cross region and is traffic has 50ms latency and it adds up, lets say you have 200 record and have 5 table that you have 1-many relation and you need to inner join, now by default using jpa/hibernate it will do 1000 select queries to get the end result will be a very costly transactions

[amatosg]

this happened to me. It takes about 3 seconds to get 20 entities. I had to use statements & executeQuery

[deepu105]

how would you suggest optimizing it in the generated code given there could be any number of relationship combinations

[amatosg]

I've tried using nativeQuery and even that doesn't help, the query has multiple joins making the retrieval of only 20 records take about 3 seconds. Using executeQuery takes about 50ms