# 應付大量新增情況:批量新增、預存程式、多執行緒
**因為客戶說要大量新增簡訊和郵件，一次至少要5萬筆，為了提高效能，開始一連串的改進。**

**1.使用批量新增**

@Transactional(propagation = Propagation.SUPPORTS)
public void saveBatchSms(List<Sms> smsList)
{
    String sqlSms = "INSERT INTO Sms (SmsSeq, SmsSysId,Content,OfficeHourSend,Status,LastUpdateTime,MaintainHost,MaintainProgramId,MainUserId,CreateDate,CreateProgramId,CreateTime,CreateUserId,DeleteFlag,Mobile,Reserved,BatchMark,InstantFlag,ExpectDeliverTime) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    Session session = this.getSession();
    Work work=new Work(){
        public void execute(Connection connection)throws SQLException{
            PreparedStatement stmtSms = null;
            try {
                if(smsList != null && !smsList.isEmpty()) {
                    long start = System.currentTimeMillis();
                    connection.setAutoCommit(false);
                    //通過JDBC API執行用於批量更新的SQL語句

                    stmtSms = connection.prepareStatement(sqlSms);

                    for(Sms bean:smsList) {
                        stmtSms.setString(1, bean.getSmsSeq());
                        stmtSms.setString(2, bean.getSmsSysId());
                        //用NString可以用Unicode存多國語言:泰文、越南文等等
                        stmtSms.setNString(3, bean.getContent());
                        stmtSms.setString(4, bean.getOfficeHourSend());
                        stmtSms.setString(5, bean.getStatus());
                        stmtSms.setTimestamp(6, new Timestamp(bean.getLastUpdateTime().getTime()));
                        stmtSms.setString(7, bean.getMaintainHost());
                        stmtSms.setString(8, bean.getMaintainProgramId());
                        stmtSms.setString(9, bean.getMainUserId());
                        stmtSms.setString(10, bean.getCreateDate());
                        stmtSms.setString(11, bean.getCreateProgramId());
                        stmtSms.setTimestamp(12, new Timestamp(bean.getCreateTime().getTime()));
                        stmtSms.setString(13, bean.getCreateUserId());
                        stmtSms.setString(14, bean.getDeleteFlag());
                        stmtSms.setString(15, bean.getMobile());
                        stmtSms.setString(16, bean.getReserved());
                        stmtSms.setString(17, bean.getBatchMark());
                        stmtSms.setString(18, bean.getInstantFlag());
                        stmtSms.setTimestamp(19, new Timestamp(bean.getExpectDeliverTime().getTime()));

                        stmtSms.addBatch();
                    }
                    stmtSms.executeBatch();

                    long end = System.currentTimeMillis();

                    System.out.println(String.format("Total time: %d 毫秒", (end - start)));
                    logger.info(String.format("Total time: %d 毫秒", (end - start)));
                    connection.commit();
                }
            }catch(SQLException e) {
                try {
                    connection.rollback();
                } catch (SQLException sqlException) {
                    sqlException.printStackTrace();
                }
            }finally {
                if (stmtSms != null) {
                    try {
                        stmtSms.close();
                    } catch (SQLException e) {
                        logger.error("preparedStatement.close() fail.", e);
                        System.out.println("preparedStatement.close() fail."+e);
                    }
                }

                if (connection != null) {
                    try {
                        connection.setAutoCommit(true);
                        connection.close();
                    } catch (SQLException e) {
                        logger.error("connection.close() fail.", e);
                        System.out.println("connection.close() fail."+ e);
                    }
                }
            }
        }
   };
   session.doWork(work);
}

**2.使用Store Procedure預存程序**
後來效能還是不夠快，所以再來加上tore Procedure

//用Store Procedure來做
@Transactional(propagation = Propagation.SUPPORTS)
public void saveSmsBatchBySP(List<Sms> smsList)
{
    Session session = this.getSession();
    Work work=new Work(){
        public void execute(Connection connection)throws SQLException{
            CallableStatement stmtSms = null;
            try {
                if(smsList != null && !smsList.isEmpty()) {
                    long start = System.currentTimeMillis();
                    connection.setAutoCommit(false);
                    //通過JDBC API執行用於批量更新的SQL語句

                    stmtSms = connection.prepareCall("{call dbo.BatchInsertSms(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");

                    for(Sms bean:smsList) {
                        stmtSms.setString(1, bean.getSmsSysId());
                        //用NString可以用Unicode存多國語言:泰文、越南文等等            
                        stmtSms.setNString(2, bean.getContent());
                        stmtSms.setString(3, bean.getOfficeHourSend());
                        stmtSms.setString(4, bean.getStatus());
                        stmtSms.setTimestamp(5, new Timestamp(bean.getLastUpdateTime().getTime()));
                        stmtSms.setString(6, bean.getMaintainHost());
                        stmtSms.setString(7, bean.getMaintainProgramId());
                        stmtSms.setString(8, bean.getMainUserId());
                        stmtSms.setString(9, bean.getCreateDate());
                        stmtSms.setString(10, bean.getCreateProgramId());
                        stmtSms.setTimestamp(11, new Timestamp(bean.getCreateTime().getTime()));
                        stmtSms.setString(12, bean.getCreateUserId());
                        stmtSms.setString(13, bean.getDeleteFlag());
                        stmtSms.setString(14, bean.getMobile());
                        stmtSms.setString(15, bean.getReserved());
                        stmtSms.setString(16, bean.getBatchMark());
                        stmtSms.setString(17, bean.getInstantFlag());
                        stmtSms.setTimestamp(18, new Timestamp(bean.getExpectDeliverTime().getTime()));

                        stmtSms.addBatch();
                    }
                    stmtSms.executeBatch();

                    long end = System.currentTimeMillis();

                    System.out.println(String.format("Total time: %d 毫秒", (end - start)));
                    logger.info(String.format("Total time: %d 毫秒", (end - start)));
                    connection.commit();
                }
            }catch(SQLException e) {
                try {
                    connection.rollback();
                } catch (SQLException sqlException) {
                    sqlException.printStackTrace();
                }
            }finally {
                if (stmtSms != null) {
                    try {
                        stmtSms.close();
                    } catch (SQLException e) {
                        logger.error("preparedStatement.close() fail.", e);
                        System.out.println("preparedStatement.close() fail."+e);
                    }
                }

                if (connection != null) {
                    try {
                        connection.setAutoCommit(true);
                        connection.close();
                    } catch (SQLException e) {
                        logger.error("connection.close() fail.", e);
                        System.out.println("connection.close() fail."+ e);
                    }
                }
            }

        }
   };
   session.doWork(work);
}

CREATE PROCEDURE  BatchInsertSms
    @SmsSysId varchar(10),
    @Content nvarchar(1000),
    @OfficeHourSend varchar(1),
    @Status varchar(1),
    @LastUpdateTime datetime,
    @MaintainHost varchar(2), 
    @MaintainProgramId varchar(20),
    @MainUserId varchar(10), 
    @CreateDate varchar(8),
    @CreateProgramId varchar(20), 
    @CreateTime datetime,
    @CreateUserId varchar(10) ,
    @DeleteFlag varchar(1),
    @Mobile nvarchar(10),
    @Reserved varchar(32),
    @BatchMark varchar(1) ,
    @InstantFlag varchar(1) ,
    @ExpectDeliverTime datetime
AS
BEGIN
   INSERT INTO Sms (
        SmsSeq, 
        SmsSysId,
        Content,
        OfficeHourSend,
        Status,
        LastUpdateTime,
        MaintainHost,
        MaintainProgramId,
        MainUserId,
        CreateDate,
        CreateProgramId,
        CreateTime,
        CreateUserId,
        DeleteFlag,
        Mobile,
        Reserved,
        BatchMark,
        InstantFlag,
        ExpectDeliverTime
    ) 
    VALUES (
        (CONVERT(VARCHAR(8), GETDATE(), 112) + RIGHT('000000' + CAST((NEXT VALUE FOR SmsMail) AS VARCHAR),6)),
        @SmsSysId,
        @Content,
        @OfficeHourSend,
        @Status,
        @LastUpdateTime,
        @MaintainHost,
        @MaintainProgramId,
        @MainUserId,
        @CreateDate,
        @CreateProgramId,
        @CreateTime,
        @CreateUserId,
        @DeleteFlag,
        @Mobile,
        @Reserved,
        @BatchMark,
        @InstantFlag,
        @ExpectDeliverTime
    )
END

**3.加上多執行緒**
速度有快了一點，但客戶想要效能再好一點，於是又加入了多執行緒。

if(StringUtils.isNotBlank(multiMobilePart1)) {
    new Thread(new Runnable() {
        public void run() {
            insertSms(multiMobilePart1.split(","),u,userId,maintainPgId);
        }
    }).start();
}

public void insertSms(String[] mobileTempArray,UserProfile u,String userId,String maintainPgId) {
    List<Sms> smsList = new ArrayList<>();
    try {
        Date dateTime = new Date();
        Date deliverTime = new Date();
        if(StringUtils.isNotBlank(showExpectDeliverTime)) {
            deliverTime = sdFormat.parse(showExpectDeliverTime);
            System.out.println("deliverTime: "+deliverTime);
        }
        System.out.println("@@@ showExpectDeliverTime:"+showExpectDeliverTime);

        for(String str:mobileTempArray)
        {
            Sms sms = new Sms();
            BeanClone.clone(smsMaintainAddVo, sms);
//                String seq = smsDao.getSeqNo();
//                logger.info("getMaxSeq:{}",seq);
//                sms.setSmsSeq(String.valueOf(seq));
            sms.setSmsSysId("EIP");
            sms.setContent(smsMaintainAddVo.getContent());
            sms.setOfficeHourSend(convertHoliday(smsMaintainAddVo.getHolidayList()));
            sms.setStatus("0");//修正 20160920
            sms.setLastUpdateTime(new Date());
            sms.setMaintainHost("C2");
            sms.setMaintainProgramId(maintainPgId);
            sms.setMainUserId(userId);
            sms.setCreateDate(DateUtils.getSysDate());
            sms.setCreateProgramId(maintainPgId);
            sms.setCreateTime(new Date());
            sms.setCreateUserId(userId);
            sms.setDeleteFlag("N");
            sms.setMobile(str);
            sms.setReserved(smsMaintainAddVo.getReserved());
            sms.setBatchMark(smsMaintainAddVo.getBatchMark());

            if(StringUtils.equals(smsMaintainAddVo.getSendTimeType(), "0")) {
                sms.setInstantFlag("Y");
                sms.setExpectDeliverTime(dateTime);
            }else if(StringUtils.equals(smsMaintainAddVo.getSendTimeType(), "1")) {
                sms.setInstantFlag("N");
                sms.setExpectDeliverTime(deliverTime);
            }
            logger.info("insret sms:"+sms);
            smsList.add(sms);
        }
        //用Store Procedur做
        smsDao.saveSmsBatchBySP(smsList);

        logger.info("##### End time:"+ new Date());
        System.out.println("##### End time:"+ new Date());
    }catch(Exception e){
        logger.error("modifyDate error:", e);
    }
}

前端頁面

$("#bt_save").click(function(){
    if(checkInput()){
        var mobile = $("#mobile").val();
        var temp = mobile.Split("\n");
        var max = temp.length;
        $("#maxCount").val(max);

        var border = Math.floor(max/10);

        jQuery("#actionType").val('A');

        var start = 0;
        var count = 1;

        /*
        if(border > 0){
            for(var i=0;i<max;i++){
                if(i % (border) == (border - 1)){

                    if(start == (border*9)){
                        $("#multiMobilePart"+count).val(temp.slice(start,max));
                        break;
                    }else{
                        $("#multiMobilePart"+count).val(temp.slice(start,(i+1)));
                        start = (i+1);
                    }
                    count++;
                }
            }
        }else{
        */
            $("#multiMobilePart1").val(temp);
        //}


        //jQuery("#multiMobile").val(temp);
        $("#smsMaintainAddform").attr("action",
            "smsMaintain").submit();

        $("input").attr("disabled",true);
        $(".btnCalendar").attr("disabled",true);
    }
});

**經過了三重加持，效能大幅躍進**
**insert 5萬筆資料，從原本的5小時進步到了57秒**
###### tags: `Java`