Java JPA 批次新增資料效能調校 batch insert performance tuning

今天同事分享一個效能調校(performance tuning)的案例如下。

原始的問題為，客戶需要上傳一百萬筆卡號的csv檔至應用程式，應用程式收到csv檔後的原始作業流程為

1. 把上傳檔案轉成卡片集合變數，此變數內有一百萬筆卡號。
2. LOOP 1000000 次
   1. 以上傳卡號至資料庫 [卡片資料表] 取得該卡號資料。

      SELECT ... FROM CARD WHERE CARD_NUMBER = :cardNubmer

   2. 若卡號不存在則新增至 [卡片資料表]。

      INSERT INTO CARD ...

   3. 將卡號號新增至 [VIP資料表]。

      INSERT INTO VIP ...

以上步驟還包含一些卡號加解密及遮罩的處理就不計入討論。其實這就是一個批次新增(Batch Insert)的問題。

在原本的步驟中，可以看到最多操作100萬 + 100萬 + 100萬共300萬次的資料庫session，這是導致此操作需要耗費好幾小時才完成，令人無法接受。

同事把步驟1與步驟2的動作合併為一段SQL執行，也就是改為

INSERT INTO CARD ... SELECT ... FROM CARD WHERE CARD_NUMBER != :cardNubmer

合併後的步驟如下：

1. 把上傳檔案轉成卡片集合變數，此變數內有一百萬筆卡號。
2. LOOP 1000000 次
   1. 以上傳卡號至資料庫 [卡片資料表] 取得該卡號資料，不存在則新增。

      INSERT INTO CARD ... SELECT ... FROM CARD WHERE CARD_NUMBER != :cardNubmer

   2. 將卡號號新增至 [VIP資料表]。

      INSERT INTO VIP ...

修改完以上後session減少為100萬 + 100萬共200萬次，但這樣仍需要許耗費太多時間。

所以同事在應用程式中把每1,000筆的SQL敘述字串合併為一筆超長的SQL字串再執行，1,000,000/1000 = 1,000，每次執行的SQL如下。

BEGIN
    INSERT INTO CARD ... SELECT ... FROM CARD WHERE CARD_NUMBER != :cardNubmer1;
    INSERT INTO CARD ... SELECT ... FROM CARD WHERE CARD_NUMBER != :cardNubmer2;
    ... 
    INSERT INTO CARD ... SELECT ... FROM CARD WHERE CARD_NUMBER != :cardNubmer999;
    INSERT INTO CARD ... SELECT ... FROM CARD WHERE CARD_NUMBER != :cardNubmer1000;
END;

這樣步驟就變為

1. 把上傳檔案轉成卡片集合變數，此變數內有一百萬筆卡號。
2. LOOP 1000000 次
   1. LOOP (1000000/1000) 次
      1. 每1000筆產生上傳卡號至資料庫 [卡片資料表] 取得該卡號資料，不存在則新增的SQL。

         BEGIN
             INSERT INTO CARD ... SELECT ... FROM CARD WHERE CARD_NUMBER != :cardNubmer1;
             INSERT INTO CARD ... SELECT ... FROM CARD WHERE CARD_NUMBER != :cardNubmer2;
             ... 
             INSERT INTO CARD ... SELECT ... FROM CARD WHERE CARD_NUMBER != :cardNubmer999;
             INSERT INTO CARD ... SELECT ... FROM CARD WHERE CARD_NUMBER != :cardNubmer1000;
         END;

   2. LOOP (1000000/1000) 次
      1. 每1000筆產生卡號號新增至 [VIP資料表] SQL。

         BEGIN
             INSERT INTO VIP (CARD_NUMBER, ... ) VALUES (cardNumber1, ...);
             INSERT INTO VIP (CARD_NUMBER, ... ) VALUES (cardNumber2, ...);
             ...
             INSERT INTO VIP (CARD_NUMBER, ... ) VALUES (cardNumber999, ...);
             INSERT INTO VIP (CARD_NUMBER, ... ) VALUES (cardNumber1000, ...);
         END;

因此最終變成1,000 + 1,000次共2,000次的session，最終可執行的時間才縮短到幾分鐘內。

因為專案是使用JPA，所以組成以上SQL並以EntityManager執行原生SQL(native query)的方式存入資料庫。

package com.abc.demo.dao;

import org.springframework.transaction.annotation.Transactional

import javax.persistence.EntityManager;
import javax.persistence.Query
import java.util.Collection;

public class CardDao {
    
    @Autowired
    private EntityManager entityManager;

    @Transactional
    public void save(Collection<String> cardNumbers) {
        String sql = generateNativeQuerySql(cardNubmers);

        Query query = entityManager.createNativeQuery(sql);
        query.executeUpdate();

        entityManager.flush();
        entityManager.clear();
    }

    private String generateNativeQuerySql(Collection<String> cardNumbers) {
        // 產生一次新增1000筆的批次新增sql。
    }
}

參考：

• Batch Insert In Java – JDBC
• Oracle 插入一百萬筆隨機產生的數值資料