Simple JPA findBy request takes 4 minutes vs 365 milliseconds of SQL Query

user_8345238 0
Viewed 394

I am using the JpaRepository interface in a Spring Boot application to map a table without foreign keys.

My pom.xml contains:

<dependency>
  <groupId>org.hibernate</groupId>
  <artifactId>hibernate-tools</artifactId>
  <version>4.3.2.Final</version>
</dependency>

So, I am using Hibernate.

My entity looks like this:

@Entity
@Table(name = "TABLE_NAME")
@NamedQuery(name = "CbmAnomalyDectOutput.findAll", query = "SELECT c FROM EntityName c")
public class EntityName implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @SequenceGenerator(name = "ID_GENERATOR", sequenceName = "MY_SEQ", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "ID_GENERATOR")
    private long id;

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

    @Column(name = "ANOMALY_PROB")
    private BigDecimal anomalyProb;

    @Column(name = "ANOMALY_SEVERITY")
    private BigDecimal anomalySeverity;

and so on!

I created the method findByAnomalyClass(String anomalyClass).

The table contains 17,050 records and the query returns about 3,000 of them.

BUT... It takes 4 minutes!!!

Comparing it with SQL query, the execution time is many times shorter.

EDIT: I activated a very verbose log and I noticed that the object org.hibernate.loader.Loader is the problem! It logs 2048 rows with the same timestamp, then other 145 and the the other rows.

THIS is the critical part. MOVING FROM A RESULTSET OF 2048 TO 2049 THE OVERALL EXECUTION TIME BECOMES VERY VERY VERY LONG!

Any suggestion?

spring-data-jpa spring-boot oracle hibernate java
1 Answers
  1. You need to make sure that the query generated by JPA is the one you expect. You can configure JPA to log queries in log file.
  2. Assuming that queries are same we can expect that response times are approximately same. One exception to this is when result set rows are not in memory already. In that case first query execution will need to read from disc into memory before returning result set.
  3. Depending on how result set is processed in Java code there may be a significant time between retrieving result set from database and completing business logic using that data. You can find out if this is the case in a debug session.
user_87346180
Related