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 语句要高效得多。以下是原因：

1. 数据库连接和资源管理

   在 Java 层的循环中调用 SQL：每次调用 SQL 都需要建立数据库连接、执行语句、处理结果，然后关闭连接。这会导致大量的开销，包括网络延迟、连接管理开销等。
   使用 foreach 进行批量插入：批量操作只需要一次数据库连接和执行。这减少了大量的连接开销和网络延迟。

2. 网络开销

   在 Java 层的循环中调用 SQL：每次调用 SQL 都需要通过网络传输数据。对于大量数据，这会导致网络带宽的消耗和延迟。
   使用 foreach 进行批量插入：批量操作可以一次性传输所有数据，极大地减少了网络传输的次数和带宽消耗。

3. 事务管理

   在 Java 层的循环中调用 SQL：每次 SQL 操作都是一个独立的事务，数据库需要为每个操作进行事务管理。这会导致事务管理的开销。
   使用 foreach 进行批量插入：所有插入操作都在一个事务中完成，数据库只需要处理一次事务管理，极大地提高了效率。

4. 数据库优化

   在 Java 层的循环中调用 SQL：数据库无法对单独的插入操作进行优化。
   使用 foreach 进行批量插入：数据库可以对批量操作进行优化，例如通过批量插入的机制来减少写入磁盘的次数，提高写入速度。

性能比较

在 Java 层的循环中调用 SQL：假设需要插入 1000 条记录，每条记录需要花费 10ms 的网络和连接管理时间，总共需要 1000 * 10ms = 10秒。
使用 foreach 进行批量插入：假设批量插入 1000 条记录只需要花费 1秒。相比之下，性能提升非常明显。