Spring Data JPA One to Many takes more time when saving-CodePudding

I could not find a better solution yet. I'm using Spring boot 2.6.7 with data jpa. I have 3 tables. Table1 has one to many relationship with Table2. Table2 has One to Many relationship with Table3. My class is as follows. Each table has composite id. It is a legacy DB2 DB, so can't change structure.

In the account table, account number field is generated using another table value. It saves last saved value in another table and increases by 1 each time when saving. So the value is set manually. And remaining all values are coming from client request.

My classes are as follows.

@Data
@Entity
@Table(name = "ACCOUNT")
public class Account {

    @EmbeddedId
    private AccountId id;

    @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)

    @JoinColumn(name = "account_type", referencedColumnName = "ACCOUNT_TYPE")
    @JoinColumn(name = "account_number", referencedColumnName = "ACCOUNT_NUMBER")
    @JoinColumn(name = "location_code", referencedColumnName = "LOCATION_CODE")
    private List<Job> jobs;

    @Column(name = "ACCOUNT_NAME")
    private String accountName;
    
    @Column(name = "STATUS")
    private String status;

    @Column(name = "CREATED_DATE")
    private Timestamp createdDate;
    
    // More fields...
}

@Data
@Embeddable
public class AccountId implements Serializable{

    private static final long serialVersionUID = 1L;

    @Column(name = "ACCOUNT_TYPE")
    private String accountType;
    
    @Column(name = "ACCOUNT_NUMBER")
    private Integer acountNumber;
    
    @Column(name = "LOCATION_CODE")
    private String locationCode;

}


@Data
@Entity
@Table(name = "ACCOUNT_JOB")
public class Job {

    @EmbeddedId
    private JobId id;

    @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
    @JoinColumn(name = "job_number", referencedColumnName = "JOB_NUMBER")
    @JoinColumn(name = "part_number", referencedColumnName = "PART_NUMBER")
    @JoinColumn(name = "job_date", referencedColumnName = "JOB_DATE")
    @JoinColumn(name = "account_type", referencedColumnName = "ACCOUNT_TYPE")
    @JoinColumn(name = "account_number", referencedColumnName = "ACCOUNT_NUMBER")
    @JoinColumn(name = "location_code", referencedColumnName = "LOCATION_CODE")
    private List<Task> tasks;

    @Column(name = "JOB_NAME")
    private String jobName;
    
    @Column(name = "JOB_STATUS_DATE")
    private Date jobStatusDate;
}


@Data
@Embeddable
public class JobId implements Serializable {

    private static final long serialVersionUID = 1L;

    @Column(name = "JOB_NUMBER", unique = true)
    private Integer jobNumber;
    
    @Column(name = "PART_NUMBER", unique = true)
    private BigDecimal partNumber;

    @Column(name = "JOB_DATE", unique = true)
    private Date jobDate;
    
    @Column(name = "ACCOUNT_TYPE", unique = true)
    private String accountType;
    
    @Column(name = "ACCOUNT_NUMBER", unique = true)
    private Integer acountNumber;
    
    @Column(name = "LOCATION_CODE", unique = true)
    private String locationCode;

}


@Data
@Entity
@Table(name = "JOB_TASK")
public class Task {

    @EmbeddedId
    private TaskId id;
    
    @Column(name = "AP_PAY_CODE")
    private String taskName;
    
    @Column(name = "TASK_STATUS_DATE")
    private Date taskStatusDate;
}


@Data
@Embeddable
public class TaskId implements Serializable {
     
    private static final long serialVersionUID = 1L;
    
    @Column(name = "TASK_NUMBER")
    private Integer taskNumber;

    @Column(name = "JOB_NUMBER")
    private Integer jobNumber;
    
    @Column(name = "PART_NUMBER")
    private BigDecimal partNumber;

    @Column(name = "JOB_DATE")
    private Date jobDate;
    
    @Column(name = "ACCOUNT_TYPE")
    private String accountType;
    
    @Column(name = "ACCOUNT_NUMBER")
    private Integer acountNumber;
    
    @Column(name = "LOCATION_CODE")
    private String locationCode;
    
}

When I try to save the account object with lesser list size, the data gets saved faster. But when the size increases it takes more time to finish saving.

When I tested for an account object with 300 jobs ans each jobs having 1 to 2 tasks, the saving took around 50 seconds, which I fel is much slower.

Is there any way to increase this? Below method I'm trying to save.

Account account = //Method which sets account, job and task objects
System.out.println(new Date()   "Account start save Test");
Account savedData = repository.save(account);
System.out.println(new Date()   "Account save Test complete");

I have tried adding below properties in application.properties, but it doesn't help

spring.jpa.properties.hibernate.jdbc.batch_size=5000
spring.jpa.properties.hibernate.order_inserts=true

CodePudding user response：

Lombok's @Data annotation by default takes all fields to the generated equals and hashCode method which can be very slow, especially with large entries. I'd recommend following vlad mihalcea's advice and implementing a custom equals and hashCode which uses only identifiers.

CodePudding user response：

This is not the exact answer. But solution specific to DB2 z/Os. The time taking is due to DB2 z/OS. It doesn't support Bulk insert. Got to know from this post. Also since I'm setting the id manually, Hibernate will initially execute select query with given id to make sure the id doesn't exist. So the number of select queries was same as the size of list.

For DB2, the only supported way is have query like below. Got from another answer in the same post.

INSERT INTO tableName (col1, col2, col3, col4, col5) 
SELECT val1, val2, val3, val4, val5 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT val1, val2, val3, val4, val5 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT val1, val2, val3, val4, val5 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT val1, val2, val3, val4, val5 FROM SYSIBM.SYSDUMMY1

So I had to dynamically create the above query for each table and execute using JdbcTemplete.

Even DB2 has limits for maximum query size. So I had to partition the list into chunks and execute it multiple times.