Java Hibernate Database speed issue

Question

I generated application using Jhipster. In start everything was working fine but as application grow tournament entity become issue regarding performances. This is my entity :

/**
 * A Tournament.
 */
@Entity
@Table(name = "tournament")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
@Document(indexName = "tournament")
public class Tournament implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequenceGenerator")
    @SequenceGenerator(name = "sequenceGenerator")
    private Long id;

    @Column(name = "name")
    private String name;

    @Column(name = "location")
    private String location;

    @Column(name = "url")
    private String url;

    @Column(name = "start_date")
    private ZonedDateTime startDate;

    @Column(name = "end_date")
    private ZonedDateTime endDate;

    @Column(name = "entry_fee")
    private Double entryFee;

    @Column(name = "prize")
    private Double prize;

    @Column(name = "goods")
    private String goods;

    @Column(name = "favorite_rating")
    private Long favoriteRating;

    @Column(name = "participants_number")
    private Integer participantsNumber;

    @Column(name = "finished")
    private Boolean finished;

    @Column(name = "view_only")
    private Boolean viewOnly;

    @Column(name = "image")
    private String image;

    @Column(name = "description")
    private String description;

    @Column(name = "teams_applied")
    private String teamsApplied;

    @Lob
    @Column(name = "schedule")
    private String schedule;

    @Lob
    @Column(name = "prize_distribution")
    private String prizeDistribution;

    @Lob
    @Column(name = "contacts")
    private String contacts;

    @Lob
    @Column(name = "rules")
    private String rules;

    @OneToMany(mappedBy = "tournament", fetch = FetchType.LAZY)
    @JsonIgnore
    @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
    private Set<Stream> streams = new HashSet<>();

    @ManyToMany
    @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
    @JoinTable(name = "tournament_platforms", joinColumns = @JoinColumn(name = "tournaments_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "platforms_id", referencedColumnName = "id"))
    private Set<Platform> platforms = new HashSet<>();

    @ManyToMany(mappedBy = "favoriteTournaments", fetch = FetchType.LAZY)
    @JsonIgnore
    @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
    private Set<User> favoriteUsers = new HashSet<>();

    @ManyToOne
    private Game game;

    @ManyToOne
    private TournamentStatus status;

    @ManyToOne
    private EntryType entryType;

    @ManyToOne
    private TournamentFormat format;

    @ManyToOne
    private Region region;

    @ManyToOne
    private GameMode gameMode;

    @ManyToOne
    private PrizeType prizeType;

    @ManyToOne
    private Organizer organizer;

    @ManyToOne
    private TournamentStage stage;

    @ManyToOne
    private HostPlatform hostPlatforms;

    @ManyToOne
    private TournamentType type;

    @ManyToOne
    private PlayType playType;

    @ManyToOne
    private Currency currency;

    @ManyToOne
    private Country country;

I am using spring JPA. Getting 20 tournaments from database takes 39 seconds. That is not acceptable. Is there any way i can reduce it to normal speed. What is reason for such a long response time ? Every many to one relation i made unidire

Have you ever executed the same query directly against the database? Might not just be an issue with hibernate. Also you have a big amount of direct dependencies which will be loaded as well and this can cause huge objects. Also you should really thinking about `lazy-loading` every dependency — XtremeBaumer, Aug 22 '18 at 11:27
"Normal speed"? I count 15 JOINs in your object - did I miss any? That is a lot of work for the query engine to do. My advice would be to denormalize your schema to see if performance improves and to not use JPA. Write SQL by hand. Black boxes are problematic when they let you down like this. — duffymo, Aug 22 '18 at 11:27

Arnaud Denoyelle · Answer 1 · 2018-08-22T12:00:28.280

4

In hibernate's implementation of JPA, @ManyToOne has a fetchType = EAGER by default and you have 14 of them.

@ManyToOne
private Country country;

That means 14 joins for each request. I highly recommend to use fetchType = LAZY for all relationships and deactivate them one by one when needed.

As a rule of thumb, you should not use more than 3 joins per request.

Also take a look at the generated request and use EXPLAIN PLAN in order to understand what the database really does and where it is costly. It will probably reveal some missing indexes on columns used as foreign keys...

edited Aug 22 '18 at 12:00

answered Aug 22 '18 at 11:29

Arnaud Denoyelle

29,980
16
92
148

1

Not to mention potentially nested joins – XtremeBaumer Aug 22 '18 at 11:30
How can i use EXPLAIN PLAN ? – user3364181 Aug 22 '18 at 11:36
What if i need them all ? – user3364181 Aug 22 '18 at 11:41
@user3364181 Usually, you don't need them all. I suspect that you don't need `Region` and `Country` each time for example. Consider using [JPA Criteria API](https://www.objectdb.com/java/jpa/query/criteria) in order to build your request and select only what you really need. – Arnaud Denoyelle Aug 22 '18 at 11:53
3

**Always** use `fetchType = LAZY`. You can make a lazy relation eager in a query (e.g. `JOIN FETCH`), but you can't make an eager relation lazy. – Kayaman Aug 22 '18 at 11:55
@user3364181 And for `EXPLAIN PLAN`, take the sql request generated by hibernate (you will have to set a parameter in order to log the generated request) then execute it manually with your favorite SQL requester. Then, add `EXPLAIN PLAN` at the beginning of the request. It will tell the database to explain the execution plan of the request. Example : `EXPLAIN PLAN SELECT * FROM MY_TABLE;` – Arnaud Denoyelle Aug 22 '18 at 11:56
@user3364181 It is a bit long to explain in the comment and it is not JPA related so I don't want to explain it in the answer. You should google about it because it is a very useful tool. – Arnaud Denoyelle Aug 22 '18 at 11:57
I places @ManyToOne(fetch = FetchType.LAZY) on every relation but it still takes 41sec. When i debug results he still gets those attributes. – user3364181 Aug 22 '18 at 12:19
If you are using Spring + Jackson to build a REST API, it is possible that the getters get called at serialization. That would cause the loading on the lazy-loaded entities. You should log the generated request in order to see what happens. Also, check that you have an index on each column that are used as a foreign key. – Arnaud Denoyelle Aug 22 '18 at 12:21
what should i do to see every query that is executed – user3364181 Aug 22 '18 at 13:05
@user3364181 see [How to print a query string with parameter values when using Hibernate](https://stackoverflow.com/questions/1710476/how-to-print-a-query-string-with-parameter-values-when-using-hibernate) – Arnaud Denoyelle Aug 22 '18 at 13:06

Java Hibernate Database speed issue

1 Answers1