JPA in spring-boot project "batch insert" very slow

2.6k views Asked by At

Hey I'm trying to insert some 800 rows with @oneToMany relationships, but it seems they are really slow. Which I don't quite understand why, since I've read some guides that said it should be fairly quick.
So I hope that some kind soul could tell me if there is something I've misunderstood and can help me increase the performance of the insertions.

The Repository:

@Repository
public interface FootnoteRepository extends CrudRepository<FootnotesEntity, Long> {

    @Query("FROM FOOTNOTES WHERE number =?1 AND FOOTNOTE_TYPE=?2 order by DATE_START desc,id asc")
    public List<FootnotesEntity> findFootnoteByNumberAndType(Long number, String transportNumber, Pageable pageable);
}

The DomainClass:

@Autowired
private EntityRepository entityRepository;

@Autowired
private FootnoteRepository footnoteRepository;
/**
 * Handles the JPA Interface
 * 
 * @param
 * @return the success of the operation
 */
@Transactional
private boolean insertUpdateFootnoteEntities(List<FootnotesEntity> footnotes) {
    boolean success = true;
    System.out.println("footnotes: " + footnotes.size());
    long start = System.currentTimeMillis();
    try {
        // TODO fix below: (does not "commit" the deletion")
        // footnoteRepository.deleteAll();

        // TODO speed optimize
        footnoteRepository.save(footnotes);

    } catch (Exception e) {
        e.printStackTrace();
        success = false;
    }
    long end = System.currentTimeMillis();
    System.out.println("time: " + (end - start));
    return success;
}

For this class I've also tried adding batchsize, and using the nonspecific repository(basicly an EntityManager)'s method: .persist(entity)

'Parent' Entity Class:

@Table(indexes = { @Index(columnList = "FOOTNOTE_NUMBER,FOOTNOTE_TYPE") }, uniqueConstraints = @UniqueConstraint(columnNames = {
        "FOOTNOTE_NUMBER", "FOOTNOTE_TYPE", "DATE_START" }))
@Entity(name = "FOOTNOTES")
@EqualsAndHashCode(callSuper = false)
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class FootnotesEntity extends BaseEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "FOOTNOTES_ID")
    protected Long id;

    @Column(name = "FOOTNOTE_NUMBER")
    protected Long number;

    @Column(name = "FOOTNOTE_TYPE")
    protected String footnoteType;

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

    @Column(name = "shortDescription", length = 2000)
    private String shortDescription;

    @Column(name = "DATE_START")
    private Date startDate;

    @Column(name = "DATE_END")
    private Date endDate;

    @OneToMany(mappedBy = "footnote", fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true)
    @OrderBy("startDate desc")
    protected List<DescriptionPeriodsEntity> descriptionPeriods;
}

'Child' Entity Class:

@Entity(name = "DESCRIPTION_PERIODS")
@EqualsAndHashCode(callSuper = false)
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class DescriptionPeriodsEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "DESCRIPTION_PERIODS_ID")
    private Long id;

    @Column(name = "DATE_START")
    private Date startDate;

    @Column(name = "DATE_END")
    private Date endDate;

    @Column(name = "DESCRIPTION", length = 5000)
    protected String description;

    @Column(name = "LANGUAGES_ID")
    protected String languagesId;

    @ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.PERSIST)
    @JoinColumn(name = "FOOTNOTES_ID")
    protected FootnotesEntity footnote;

}

Current runtime: Above 4 minutes(276642ms) to do the inserts (796 footnote rows and 900 descriptionPeriodes)

0

There are 0 answers