SQL批量插入的优化心得
pkgRiskDataVOList = mapper.getBuyAndSaleFee(ImmutableMap.ofEntries(kv("iTradeDate", iTradeDate)));
String fnFmtNumber16 = pkiPub.fnFmtNumber(pkgRiskDataVOList.getBuyFee(), 6);
String fnFmtNumber17 = pkiPub.fnFmtNumber(pkgRiskDataVOList.getSaleFee(), 6);
在把存储过程改成sql+java。老sql中定义了函数来对结果进行trim之后批量插入，但是新的sql考虑到迁移性没有定义函数。所以在java中进行了trim，但这样的话就没法用原来批量插入的sql。新sql只有单行插入。本来要用循环的，但是考虑到清算需要效率，所以考虑用foreach改写：
List<PkgRiskDataVO> pkgRiskDataVOList = mapper.getLongAndShortAmtAndTodayPrice(ImmutableMap.ofEntries(kv("iTradeDate", iTradeDate), kv("vCapitalTotalAccount", vCapitalTotalAccount)));
String finalVCapitalTotalAccount = vCapitalTotalAccount;
List<Map<String, Object>> formattedSqlParams = pkgRiskDataVOList.stream().map(pkgRiskDataVO -> {
String fnFmtNumber10 = pkiPub.fnFmtNumber(pkgRiskDataVO.getLongAmt(), 6);
String fnFmtNumber11 = pkiPub.fnFmtNumber(pkgRiskDataVO.getShortAmt(), 6);
String fnFmtNumber12 = pkiPub.fnFmtNumber(pkgRiskDataVO.getTodayPrice(), 6);
Map<String, Object> dataMap = new HashMap<>();
dataMap.put("fnFmtNumber10", fnFmtNumber10);
dataMap.put("fnFmtNumber11", fnFmtNumber11);
dataMap.put("fnFmtNumber12", fnFmtNumber12);
dataMap.put("cnSplitChar", cnSplitChar);
dataMap.put("cnFiCdp", cnFiCdp);
dataMap.put("iTradeDate", iTradeDate);
dataMap.put("vCapitalTotalAccount", finalVCapitalTotalAccount);
return dataMap;
}).collect(Collectors.toList());
rowcountGen = mapper.spGenRiskinitdataSql13(formattedSqlParams);
sql
<insert id="spGenRiskinitdataSql13" parameterType="map">
INSERT INTO tmp_data_gen (ROWNUM, COLUMN2, CN_FI_CV)
<foreach collection="list" item="item" separator=",">
SELECT ROWNUM,
TRIM(cust_no) || #{item.cnSplitChar} || TRIM(variety_code) ||
#{item.cnSplitChar} || long_posi || #{item.cnSplitChar} ||
#{item.fnFmtNumber10} || #{item.cnSplitChar} ||
short_posi || #{item.cnSplitChar} ||
#{item.fnFmtNumber11} || #{item.cnSplitChar} ||
#{item.fnFmtNumber12} || #{item.cnSplitChar} || '0' ||
#{item.cnSplitChar} || TRIM(bank_acc),
#{item.cnFiCdp}
FROM (SELECT a.cust_no,
d.bank_acc,
a.variety_code,
a.long_posi,
a.long_posi * c.today_price * b.exch_unit long_amt,
a.short_posi,
a.short_posi * c.today_price * b.exch_unit short_amt,
c.today_price
FROM cli_defer_hold a,
defer_variety b,
settle_price c,
cust_basicinfo d
WHERE a.tx_date = TRIM(#{item.iTradeDate})
AND a.variety_code = b.variety_code
AND a.tx_date = c.tx_date
AND a.variety_code = c.variety_code
AND a.cust_no = d.cust_no
AND a.cust_no != #{item.vCapitalTotalAccount}
AND NOT (nvl(a.long_posi, 0) = 0 AND nvl(a.short_posi, 0) = 0)
UNION ALL
SELECT a.cust_no,
d.bank_acc,
a.variety_code,
a.long_posi,
a.long_posi * c.today_price * b.exch_unit long_amt,
a.short_posi,
a.short_posi * c.today_price * b.exch_unit short_amt,
c.today_price
FROM cli_quote_hold a,
quote_variety b,
settle_price c,
cust_basicinfo d
WHERE a.tx_date = TRIM(#{item.iTradeDate})
AND a.variety_code = b.variety_code
AND a.tx_date = c.tx_date
AND a.variety_code = c.variety_code
AND a.cust_no = d.cust_no
AND a.cust_no != #{item.vCapitalTotalAccount}
AND NOT (nvl(a.long_posi, 0) = 0 AND nvl(a.short_posi, 0) = 0))
</foreach>
</insert>
在数据库操作中，使用 foreach 在 SQL 语句中进行批量插入，比在 Java 层的循环中一次次地调用 SQL 语句要高效得多。以下是原因：
-
数据库连接和资源管理
在 Java 层的循环中调用 SQL：每次调用 SQL 都需要建立数据库连接、执行语句、处理结果，然后关闭连接。这会导致大量的开销，包括网络延迟、连接管理开销等。
使用 foreach 进行批量插入：批量操作只需要一次数据库连接和执行。这减少了大量的连接开销和网络延迟。 -
网络开销
在 Java 层的循环中调用 SQL：每次调用 SQL 都需要通过网络传输数据。对于大量数据，这会导致网络带宽的消耗和延迟。
使用 foreach 进行批量插入：批量操作可以一次性传输所有数据，极大地减少了网络传输的次数和带宽消耗。 -
事务管理
在 Java 层的循环中调用 SQL：每次 SQL 操作都是一个独立的事务，数据库需要为每个操作进行事务管理。这会导致事务管理的开销。
使用 foreach 进行批量插入：所有插入操作都在一个事务中完成，数据库只需要处理一次事务管理，极大地提高了效率。 -
数据库优化
在 Java 层的循环中调用 SQL：数据库无法对单独的插入操作进行优化。
使用 foreach 进行批量插入：数据库可以对批量操作进行优化，例如通过批量插入的机制来减少写入磁盘的次数，提高写入速度。
性能比较
在 Java 层的循环中调用 SQL：假设需要插入 1000 条记录，每条记录需要花费 10ms 的网络和连接管理时间，总共需要 1000 * 10ms = 10秒。
使用 foreach 进行批量插入：假设批量插入 1000 条记录只需要花费 1秒。相比之下，性能提升非常明显。
