Spring&Hibernate performace tunning-CodePudding

I have an Entity like the code down here where for every phrase I can have many translations in different languages.

The problem is that when I fetch the translations Hibernate makes a query to the database for every phrase.

So if I have 1000 translations Hibernate will automatically make 1 query for translations and 1000 for the phrases table.

But this is very slow as compared to a JOIN and a single query: "SELECT * FROM ad_translations a JOIN ad_phrase ap ON (ap.id = a.id_ad_phrase)"

What are the options in this case? Should I use Native SQL or is there a better way?

@Entity
@Table(name="ad_translations")
public class Translations implements Serializable  {
    ...
    @ManyToOne
    @JoinColumn(name="id_ad_phrase")
    private Phrase idAdPhrase;

    @ManyToOne
    @JoinColumn(name="id_ad_lang")
    private Lang idAdLang;    
    ...
}

UPDATE: I read 3 possible solutions here https://hackernoon.com/3-ways-to-deal-with-hibernate-n1-problem But all seem to be imperfect as:

the first solution is NativeSQL and it must be the correct one from the point of view of the performance but going this way I have a lot of code to write when I fetch the data as I must manually create the objects for Lang and Phrase and populate them from the result of the query.
the second solution(@BatchSize) involves too many queries
the third solution(@Fetch(FetchMode.SUBSELECT)) is obviously not as good as the first regarding the performance

FetchType.Lazy will not help as I use this entity in a REST application and all data will be fetched at serialization time.

I'm not sure how Projections affect the N 1 problem, seems that they only help to make a selection with a custom number of columns but the number of queries remains the same.

I don't know why Hibernate doesn't have an auto JOIN option so we can use the first solution with very little code written.

I'll go for the native SQL option.

CodePudding user response：

The problem with *ToOne annotations is that the default fetch type is FetchType.EAGER, which means, in this particular case, whenever you fetch Translations, its Phrase and Lang will be fetched as well.

If you change the fetch type to FetchType.Lazy, the associated entities would be fetched on getter execution. Be careful, because if you return the Translations object as a response, then the associated entities would be fetched during serialization.

As a starting point, I would suggest you use projections, to only retrieve the properties that you need. Of course, there are many optimizations you can find on the internet, here for example.

CodePudding user response：

we should always go for the Native SQL first. But we can use it in your case with the ManyToOne annotation FetchType.LAZY will definitely reduce the time to fetch information from the database.