Search code examples
javasqlhibernatesentry

How to analyze JPA/Hibernate database queries for efficiency

We are using Spring Boot with Hibernate to connect to a postgresql database and noticed that some queries are much slower than expected. Are there any good tools or methods to find out why this is happening? We are using Sentry, so I have some insight into which statements are the problem, e.g. here:

enter image description here

We can see that BookingLegRepository.findByStartTimewindowStartBetweenAndDistributorId takes a long time and the other repository fetches are quite fast. ScheduledLegRepository.findByStartTimeBetweenAndAssignment_Distributor_Id fetches a similar amount of objects.

Now, I am wondering if this is because the query is not efficiently defined:

@Query("select b from BookingLegEntity b left join fetch b.belongsTo where b.startTimewindowStart between ?1 and ?2 and b.distributor.id = ?3")
List<BookingLegEntity> findByStartTimewindowStartBetweenAndDistributorId(Instant from, Instant to, UUID distributorId);

or if this is because of some caching Hibernate does in the background which make the subsequent calls faster (scheduled-legs and booking-legs are associated with each other).

Also, I cannot imagine that the database itself is the reason for the slow query, so I have been wondering if it is the creation of the Java objects by Hibernate that takes too much time.

Is there any good way how we can get better insights into our problem?

Solution

  • Is there any good way how we can get better insights into our problem?

    Well, yes, of course the very first step is to log the actual SQL executed by Hibernate, either by setting the properties:

    <property name="hibernate.show_sql">true</property>
<property name="hibernate.format_sql">true</property>
<property name="hibernate.highlight_sql">true</property>

    Or by setting the log category org.hibernate.SQL to debug.

    You absolutely cannot possibly analyze the performance of data access code by treating a Spring repository thingy as a black box. Nor is any Java monitoring or profiling tool likely to be very useful, since what you really need to do is minimize roundtrips to the database server.