Why is JPA query so slow?

Question

I am implementing queries in my web application with JPA repositories. The two main tables I am querying from are FmReportTb and SpecimenTb.

Here are the two entity classes (only important attributes are listed).

//FmReportTb.java
@Entity
@Table(name="FM_REPORT_TB")
public class FmReportTb implements Serializable {

    @Column(name="ROW_ID")
    private long rowId;

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

    @Column(name="FR_FULL_NAME")
    private String frFullName;
    @OneToOne
    @JoinColumn(name="SPECIMEN_ID")
    private SpecimenTb specimenTb;

FmReportTb has OneToOne relationship with SpecimenTb.

@Entity
@Table(name="SPECIMEN_TB")
public class SpecimenTb implements Serializable {
    private String mrn;
    @OneToOne(mappedBy="specimenTb", cascade=CascadeType.ALL)
    private FmReportTb fmReportTb;

The query I am working on is to find all records in FmReportTb and show a few attributes from FmReportTb plus mrn from SpecimenTb. Here is my JPA repository for FmReportTb:

@Repository
public interface FmReportRepository extends JpaRepository<FmReportTb, Long> {
    @Query("select f from FmReportTb f where f.deleteTs is not null")
    public List<FmReportTb> findAllFmReports();

Since, I am only showing part of the attributes from FmReportTb and one attribute from SpecimenTb, I decided to create a Value Object for FmReportTb. The constructor of the VO class assigns attributes from FmReportTb and grabs mrn attribute from SpecimenTb based on the OneToOne relationship. Another reason for using VO is because table FmReportTb has a lot of OneToMany children entities. For this particular query, I don't need any of them.

public class FmReportVO {
    private String frBlockId;
    private Date frCollectionDate;
    private String frCopiedPhysician;
    private String frDiagnosis;
    private String frFacilityName;
    private String frFullName;
    private String frReportId;
    private String filepath;
    private String mrn;

    public FmReportVO(FmReportTb fmReport) {
        this.frBlockId = fmReport.getFrBlockId();
        this.frCollectionDate = fmReport.getFrCollectionDate();
        this.frCopiedPhysician = fmReport.getFrCopiedPhysician();
        this.frDiagnosis = fmReport.getFrDiagnosis();
        this.frFacilityName = fmReport.getFrFacilityName();
        this.frFullName = fmReport.getFrFullName();
        this.frReportId = fmReport.getFrReportId();
        this.mrn = fmReport.getSpecimenTb().getMrn();
    }

I implemented findall method in servicebean class to return a list of FmReportTb VOs.

//FmReportServiceBean.java
    @Override
    public List<FmReportVO> findAllFmReports() {
        List<FmReportTb> reports = fmReportRepository.findAllFmReports();
        if (reports == null) {
            return null;
        }
        List<FmReportVO> fmReports = new ArrayList<FmReportVO>();
        for (FmReportTb report : reports) {
            FmReportVO reportVo = new FmReportVO(report);
            String filepath = fileLoadRepository.findUriByFileLoadId(report.getFileLoadId().longValue());
            reportVo.setFilepath(filepath);
            fmReports.add(reportVo);
        }
        return fmReports;
    }

Lastly, my controller looks like this:

@RequestMapping(
        value = "/ristore/foundation/",
        method = RequestMethod.GET,
        produces = "application/json")
public ResponseEntity<List<FmReportVO>> getAllFmReports() {
    List<FmReportVO> reports = ristoreService.findAllFmReports();
    if (reports == null) {
        return new ResponseEntity<List<FmReportVO>>(HttpStatus.NOT_FOUND);
    }
    return new ResponseEntity<List<FmReportVO>>(reports, HttpStatus.OK);
}

There are about 200 records in the database. Surprisingly, it took almost 2 full seconds to retrieve all the records in JSON. Even though I did not index all the tables, this is way too slow. Similar query takes about probably a few ms on the database directly. Is it because I am using Value Objects or JPA query tends to be this slow?

EDIT 1 This may have to do with the fact that FmReportTb has almost 20 OneToMany entities. Although the fetchmode of these child entities are set to LAZY, JPA Data repository tends to ignore the fetchmode. So I ended up using NamedEntityGraph to specify the attributes EAGER. This next section is added to the head of my FmReportTb entity class.

@Entity
@NamedEntityGraph(
        name = "FmReportGraph", 
        attributeNodes = { 
          @NamedAttributeNode("fileLoadId"),
          @NamedAttributeNode("frBlockId"),
          @NamedAttributeNode("frCollectionDate"),
          @NamedAttributeNode("frDiagnosis"),
          @NamedAttributeNode("frFullName"),
          @NamedAttributeNode("frReportId"),
          @NamedAttributeNode("specimenTb")})
@Table(name="FM_REPORT_TB")

And then @EntityGraph("FmReportGraph") was added before the JPA repository query to find all records. After doing that, the performance is improved a little bit. Now fetching 1500 records only takes about 10 seconds. However, it still seems too slow given each json object is fairly small.

Seems you don't use any `join` clause when you do direct query. That's why direct query works so fast. But hibernate will use `join`. And query with `join` will work very slow, if foreign key's columns are not indexed. Another suspection is your `findUriByFileLoadId` method. Possible delays are there. — Ken Bekov, Sep 14 '16 at 03:13
@KenBekov This may be caused by the many onetomany entities are treated EAGER by JPA data repository (see my edits to the original post). By using EntityGraph to select the EAGER attributes, the time for query has been reduced but still quite slow. Is there a way to ask the application to print out the actual hibernate SQL that has been executed? Or even better to record the time spent on each one of them. — ddd, Sep 14 '16 at 04:10
Hibernate has `true`. You can add it hibernate configuration. Did you add indexes for foreign key's reference columns? — Ken Bekov, Sep 14 '16 at 07:01

score 0 · Answer 1 · answered Mar 03 '20 at 15:14

Answering for the benefit of others with slow JPA queries...

As @Ken Bekov hints in the comments, foreign keys can help a lot with JPA.

I had a couple of tables with a many to one relationship - a query of 100,000 records was taking hours to perform. Without any code changes I reduced this to seconds just by adding a foreign key.

In phpMyAdmin you do this by creating a Relationship from the "many" table to the "one" table. For a detailed explanation see this question: Setting up foreign keys in phpMyAdmin? and the answer by @Devsi Odedra

Why is JPA query so slow?

1 Answers1