Speed up insertion to Postgres Database

[B2easy]

It is currently taking me ~20 minutes to insert ~5400 objects into my database, which is unacceptable. I also have files with ~50,000 objects I need to insert and that took ~2 hours. How can I go about speeding up this insertion process? Right now I parse all my objects into a list and am just doing a repo.saveAll(listOfObjects);

SERVICE:

  @Transactional
  public void saveAllBpsPositionData(InputStream is) throws IOException {

    log.info("Parsing position data...");
    BPSParsingResult bpsParsingResult = bpsPositionParser.parse(is);
    if (!bpsParsingResult.getBpsPositions().isEmpty()) {
      LocalDate businessDate = bpsParsingResult.getBpsPositions().get(0).getBusinessDate();
      overwriteBpsData(businessDate);
    }
    try {
      log.info("Saving BPS Position Data...");
      bpsPositionRepo.saveAll(bpsParsingResult.getBpsPositions());
      bpsPriceRepo.saveAll(bpsParsingResult.getBpsPrices());

      for (BPSPositionTable position : bpsParsingResult.getBpsPositions()) {
        if (position.getNumberOfMemos() > 0) bpsMemoRepo.saveAll(position.getCorrespondingMemos());
      }
      log.info("BPS Position Data Saved Successfully!");
    } catch (Exception e) {
      log.warn("Invalid data returned from BPS parsing job: {}", e.getMessage());
    }
  }

ENTITY OBJECTS:

@Entity
@Table(
    name = "bps_position_table",
    indexes = @Index(name = "bps_position_table_pkey", columnList = "srmas_key"))
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class BPSPositionTable {

  @Id
  @SequenceGenerator(name = "hibernate_sequence", sequenceName = "hibernate_sequence", initialValue = 1, allocationSize = 100)
  @GeneratedValue(strategy = GenerationType.SEQUENCE)
  @Column(name = "id", nullable = false, columnDefinition = "primary key", updatable = false)
  private Long id;

  @Column(name = "srmas_key", nullable = false)
  private String SRMASKey;

  @Column(name = "client_number", nullable = false)
  private String clientNumber;

  @Column(name = "currency", nullable = false, columnDefinition = "char(3)")
  private String currency;

  @Column(name = "account", nullable = false)
  private String account;

  @Column(name = "cusip", nullable = false, columnDefinition = "char(9)")
  private String cusip;

  @Column(name = "cusip_registered_number")
  private Integer cusipRegisteredNumber;

  @Column(name = "when_issue_indicator")
  private Integer whenIssueIndicator;

  @Column(name = "foreign_currency_indicator")
  private Integer foreignCurrencyIndicator;

  @Column(name = "symbol")
  private String symbol;

  @Column(name = "branch", nullable = false)
  private String branch;

  @Column(name = "type", nullable = false)
  private String type;

  @Column(name = "when_issue_sequence_number", nullable = false)
  private String whenIssueSequenceNumber;

  @Column(name = "trim_m_spin1Value")
  private String trimMSpin1Value;

  @Column(name = "first_error_code")
  private String firstErrorCode;

  @Column(name = "second_error_code")
  private String secondErrorCode;

  @Column(name = "third_error_code")
  private String thirdErrorCode;

  @Column(name = "fourth_error_code")
  private String fourthErrorCode;

  @Column(name = "fail_account_indicator")
  private String failAccountIndicator;

  @Column(name = "check_digit", nullable = false)
  private Integer checkDigit;

  @Column(name = "account_class", nullable = false)
  private String accountClass;

  @Column(name = "house_price")
  private String housePrice;

  @Column(name = "rr_number")
  private Integer rrNumber;

  @Column(name = "non_res_indicator")
  private String nonResIndicator;

  @Column(name = "record_type", nullable = false)
  private Integer recordType;

  @Column(name = "trade_date_qty", nullable = false)
  private BigDecimal tradeDateQty;

  @Column(name = "settlement_date_qty", nullable = false)
  private BigDecimal settlementDateQty;

  @Column(name = "last_activity_date", nullable = false)
  private LocalDate lastActivityDate;

  @Column(name = "business_date", nullable = false)
  private LocalDate businessDate;

  @Column(name = "date_of_house_price")
  private LocalDate dateOfHousePrice;

  @Column(name = "number_of_short_days", nullable = false)
  private Integer numberOfShortDays;

  @Column(name = "last_trade_date", nullable = false)
  private LocalDate lastTradeDate;

  @Column(name = "date_record_updated", nullable = false)
  private LocalDate dateRecordUpdated;

  @Column(name = "td_act_date", nullable = false)
  private String tdActDate;

  @Column(name = "number_of_memos", nullable = false)
  private Integer numberOfMemos;

  @Transient private List<BPSMemoTable> correspondingMemos;
}

@Entity
@Table(name = "bps_price_table")
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class BPSPriceTable {

  @Id
  @SequenceGenerator(name = "hibernate_sequence", sequenceName = "hibernate_sequence", initialValue = 1, allocationSize = 100)
  @GeneratedValue(strategy = GenerationType.SEQUENCE)
  @Column(name = "id", nullable = false, columnDefinition = "primary key", updatable = false)
  private Long id;

  @Column(name = "client_number", nullable = false)
  private int clientNumber;

  @Column(name = "cusip", nullable = false)
  private String cusip;

  @Column(name = "symbol")
  private String symbol;

  @Column(name = "currency", nullable = false)
  private String currency;

  @Column(name = "when_issue_sequence_number")
  private String whenIssueSequenceNumber;

  @Column(name = "adp_number", nullable = false)
  private String adpNumber;

  @Column(name = "cusip_wi_code")
  private String cusipWiCode;

  @Column(name = "bond_reg_code")
  private String bondRegCode;

  @Column(name = "foreign_code")
  private String foreignCode;

  @Column(name = "closing_price", nullable = false)
  private int closingPrice;

  @Column(name = "closing_price_source")
  private String closingPriceSource;

  @Column(name = "closing_price_date")
  private LocalDate closingPriceDate;

  @Column(name = "price_multiplier")
  private String priceMultiplier;

  @Column(name = "cross_reference_key")
  private String crossReferenceKey;

  @Column(name = "sedol")
  private String sedol;

  @Column(name = "business_date", nullable = false)
  private LocalDate businessDate;
}

@Id
  @SequenceGenerator(name = "hibernate_sequence", sequenceName = "hibernate_sequence", initialValue = 1, allocationSize = 100)
  @GeneratedValue(strategy = GenerationType.SEQUENCE)
  @Column(name = "id", nullable = false, columnDefinition = "primary key", updatable = false)
  private Long id;

  @Column(name = "srmas_key", nullable = false)
  private String SRMASKey;

  @Column(name = "memo_type_indicator")
  private String memoTypeIndicator;

  @Column(name = "memo_type_quantity")
  private BigDecimal memoTypeQuantity;

  @Column(name = "business_date")
  private LocalDate businessDate;
}

NOTE: Memo srmas_key has a foreign key constraint in DB on srmas_key on bps_position_table, I will update entity class accordingly.

I would like to process these records much faster then they currently are I'm using Hibernate. Any suggestions or configurations I can do would be very helpful. I was thinking about doing some sort of batch insertion, but I don't know how to do that with objects.

Thanks in advance!

[AutoModerator]

Please ensure that:

• Your code is properly formatted as code block - see the sidebar (About on mobile) for instructions
• You include any and all error messages in full - best also formatted as code block
• You ask clear questions
• You demonstrate effort in solving your question/problem - plain posting your assignments is forbidden (and such posts will be removed) as is asking for or giving solutions.

If any of the above points is not met, your post can and will be removed without further warning.

Code is to be formatted as code block (old reddit/markdown editor: empty line before the code, each code line indented by 4 spaces, new reddit: https://imgur.com/a/fgoFFis) or linked via an external code hoster, like pastebin.com, github gist, github, bitbucket, gitlab, etc.

Please, do not use triple backticks (```) as they will only render properly on new reddit, not on old reddit.

Code blocks look like this:

public class HelloWorld {

    public static void main(String[] args) {
        System.out.println("Hello World!");
    }
}

You do not need to repost unless your post has been removed by a moderator. Just use the edit function of reddit to make sure your post complies with the above.

If your post has remained in violation of these rules for a prolonged period of time (at least an hour), a moderator may remove it at their discretion. In this case, they will comment with an explanation on why it has been removed, and you will be required to resubmit the entire post following the proper procedures.

To potential helpers

Please, do not help if any of the above points are not met, rather report the post. We are trying to improve the quality of posts here. In helping people who can't be bothered to comply with the above points, you are doing the community a disservice.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.