How to Speed Up Excel-to-Database Imports with Async and Thread‑Pool Tuning in Java

In development we often need to import data from Excel into a database.

1. Typical approach

Read the Excel file with POI.

Use the file name as the table name, column headers as column names, and concatenate SQL statements.

Insert the data via JDBC or MyBatis.

When processing many large files this method becomes very slow; reading a 100 000‑row Excel file took 191 seconds.

private void readXls(String filePath, String filename) throws Exception {
    @SuppressWarnings("resource")
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(filePath));
    // read first sheet
    XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
    int maxRow = sheet.getLastRowNum();
    StringBuilder insertBuilder = new StringBuilder();
    insertBuilder.append("insert into ").append(filename).append(" ( UUID,");
    XSSFRow row = sheet.getRow(0);
    for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
        insertBuilder.append(row.getCell(i)).append(",");
    }
    insertBuilder.deleteCharAt(insertBuilder.length() - 1);
    insertBuilder.append(" ) values ( ");
    StringBuilder stringBuilder = new StringBuilder();
    for (int i = 1; i <= maxRow; i++) {
        XSSFRow xssfRow = sheet.getRow(i);
        String id = "";
        String name = "";
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            if (j == 0) {
                id = xssfRow.getCell(j) + "";
            } else if (j == 1) {
                name = xssfRow.getCell(j) + "";
            }
        }
        boolean flag = isExisted(id, name);
        if (!flag) {
            stringBuilder.append(insertBuilder);
            stringBuilder.append('\'').append(uuid()).append('\'').append(",");
            for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                stringBuilder.append('\'').append(value).append('\'').append(",");
            }
            stringBuilder.deleteCharAt(stringBuilder.length() - 1);
            stringBuilder.append(" )
");
        }
    }
    List<String> collect = Arrays.stream(stringBuilder.toString().split("
")).collect(Collectors.toList());
    int sum = JdbcUtil.executeDML(collect);
}

private static boolean isExisted(String id, String name) {
    String sql = "select count(1) as num from " + static_TABLE + " where ID = '" + id + "' and NAME = '" + name + "'";
    String num = JdbcUtil.executeSelect(sql, "num");
    return Integer.valueOf(num) > 0;
}

private static String uuid() {
    return UUID.randomUUID().toString().replace("-", "");
}

2. Optimizations

Optimization 1: Cache all data in a map before inserting

Speed improves dramatically.

Optimization 2: Use async + multithreading to read large Excel files in batches

Optimization 3: Process each Excel file with its own async task for massive file sets

After applying double async, import time dropped from 191 seconds to 2 seconds.

1. readExcelCacheAsync controller

@RequestMapping(value = "/readExcelCacheAsync", method = RequestMethod.POST)
@ResponseBody
public String readExcelCacheAsync() {
    String path = "G:\\测试\\data\\";
    try {
        // cache all data before reading Excel
        USER_INFO_SET = getUserInfo();
        File file = new File(path);
        String[] xlsxArr = file.list();
        for (int i = 0; i < xlsxArr.length; i++) {
            File fileTemp = new File(path + "\\" + xlsxArr[i]);
            String filename = fileTemp.getName().replace(".xlsx", "");
            readExcelCacheAsyncService.readXls(path + filename + ".xlsx", filename);
        }
    } catch (Exception e) {
        logger.error("|#ReadDBCsv|#异常: ", e);
        return "error";
    }
    return "success";
}

2. Batch read large Excel

@Async("async-executor")
public void readXls(String filePath, String filename) throws Exception {
    @SuppressWarnings("resource")
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(filePath));
    XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
    int maxRow = sheet.getLastRowNum();
    logger.info(filename + ".xlsx，一共" + maxRow + "行数据！");
    StringBuilder insertBuilder = new StringBuilder();
    insertBuilder.append("insert into ").append(filename).append(" ( UUID,");
    XSSFRow row = sheet.getRow(0);
    for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
        insertBuilder.append(row.getCell(i)).append(",");
    }
    insertBuilder.deleteCharAt(insertBuilder.length() - 1);
    insertBuilder.append(" ) values ( ");
    int times = maxRow / STEP + 1;
    for (int time = 0; time < times; time++) {
        int start = STEP * time + 1;
        int end = STEP * time + STEP;
        if (time == times - 1) {
            end = maxRow;
        }
        if (end + 1 - start > 0) {
            readExcelDataAsyncService.readXlsCacheAsyncMybatis(sheet, row, start, end, insertBuilder);
        }
    }
}

3. Async batch insert

@Async("async-executor")
public void readXlsCacheAsync(XSSFSheet sheet, XSSFRow row, int start, int end, StringBuilder insertBuilder) {
    StringBuilder stringBuilder = new StringBuilder();
    for (int i = start; i <= end; i++) {
        XSSFRow xssfRow = sheet.getRow(i);
        String id = "";
        String name = "";
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            if (j == 0) {
                id = xssfRow.getCell(j) + "";
            } else if (j == 1) {
                name = xssfRow.getCell(j) + "";
            }
        }
        boolean flag = isExisted(id, name);
        if (!flag) {
            stringBuilder.append(insertBuilder);
            stringBuilder.append('\'').append(uuid()).append('\'').append(",");
            for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                stringBuilder.append('\'').append(value).append('\'').append(",");
            }
            stringBuilder.deleteCharAt(stringBuilder.length() - 1);
            stringBuilder.append(" )
");
        }
    }
    List<String> collect = Arrays.stream(stringBuilder.toString().split("
")).collect(Collectors.toList());
    if (collect != null && collect.size() > 0) {
        int sum = JdbcUtil.executeDML(collect);
    }
}

private boolean isExisted(String id, String name) {
    return ReadExcelCacheAsyncController.USER_INFO_SET.contains(id + "," + name);
}

4. Async thread‑pool utility

@Async works as follows

Add @Async on a method to make it asynchronous.

Add @Async on a class to make all its methods asynchronous.

The class must be managed by Spring.

Enable async processing with @EnableAsync in a configuration class.

If no custom thread pool is specified, Spring uses SimpleAsyncTaskExecutor.

Default thread‑pool configuration

Core pool size: 8

Maximum pool size: Integer.MAX_VALUE

Queue: LinkedBlockingQueue (capacity Integer.MAX_VALUE)

Keep‑alive time: 60 s

Rejection policy: AbortPolicy

In CPU‑bound scenarios set pool size to N (or N+1); for I/O‑bound set to 2N. Adjust based on load testing.

spring:
  task:
    execution:
      pool:
        max-size: 10
        core-size: 5
        keep-alive: 3s
        queue-capacity: 1000
        thread-name-prefix: my-executor

@EnableAsync
@Configuration
public class AsyncTaskConfig {
    @Bean("my-executor")
    public Executor firstExecutor() {
        ThreadFactory threadFactory = new ThreadFactoryBuilder().setNameFormat("my-executor").build();
        int curSystemThreads = Runtime.getRuntime().availableProcessors() * 2;
        ThreadPoolExecutor threadPool = new ThreadPoolExecutor(curSystemThreads, 100,
                200, TimeUnit.SECONDS,
                new LinkedBlockingQueue<>(), threadFactory);
        threadPool.allowsCoreThreadTimeOut();
        return threadPool;
    }

    @Bean("async-executor")
    public Executor asyncExecutor() {
        ThreadPoolTaskExecutor taskExecutor = new ThreadPoolTaskExecutor();
        taskExecutor.setCorePoolSize(24);
        taskExecutor.setMaxPoolSize(200);
        taskExecutor.setQueueCapacity(50);
        taskExecutor.setKeepAliveSeconds(200);
        taskExecutor.setThreadNamePrefix("async-executor-");
        taskExecutor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
        taskExecutor.initialize();
        return taskExecutor;
    }
}

3. Core thread size tuning

Setting CorePoolSize to the number of CPU cores (or cores + 1) works well for CPU‑bound tasks; for I/O‑bound tasks use 2 × cores. Over‑sizing the pool creates excessive context switching and degrades performance.

4. Using EasyExcel for import

ReadEasyExcelController

@RequestMapping(value = "/readEasyExcel", method = RequestMethod.POST)
@ResponseBody
public String readEasyExcel() {
    try {
        String path = "G:\\测试\\data\\";
        String[] xlsxArr = new File(path).list();
        for (int i = 0; i < xlsxArr.length; i++) {
            String filePath = path + xlsxArr[i];
            File fileTemp = new File(path + xlsxArr[i]);
            String fileName = fileTemp.getName().replace(".xlsx", "");
            List<UserInfo> list = new ArrayList<>();
            EasyExcel.read(filePath, UserInfo.class, new ReadEasyExeclAsyncListener(readEasyExeclService, fileName, batchCount, list))
                    .sheet().doRead();
        }
    } catch (Exception e) {
        logger.error("readEasyExcel 异常：", e);
        return "error";
    }
    return "suceess";
}

ReadEasyExeclAsyncListener

public class ReadEasyExeclAsyncListener implements ReadListener<UserInfo> {
    private ReadEasyExeclService readEasyExeclService;
    private String TABLE_NAME;
    private int BATCH_COUNT;
    private List<UserInfo> LIST;

    public ReadEasyExeclAsyncListener(ReadEasyExeclService readEasyExeclService, String tableName, int batchCount, List<UserInfo> list) {
        this.readEasyExeclService = readEasyExeclService;
        this.TABLE_NAME = tableName;
        this.BATCH_COUNT = batchCount;
        this.LIST = list;
    }

    @Override
    public void invoke(UserInfo data, AnalysisContext analysisContext) {
        data.setUuid(uuid());
        data.setTableName(TABLE_NAME);
        LIST.add(data);
        if (LIST.size() >= BATCH_COUNT) {
            readEasyExeclService.saveDataBatch(LIST);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        if (LIST.size() > 0) {
            readEasyExeclService.saveDataBatch(LIST);
        }
    }

    public static String uuid() {
        return UUID.randomUUID().toString().replace("-", "");
    }
}

ReadEasyExeclServiceImpl

@Service
public class ReadEasyExeclServiceImpl implements ReadEasyExeclService {
    @Resource
    private ReadEasyExeclMapper readEasyExeclMapper;

    @Override
    public void saveDataBatch(List<UserInfo> list) {
        // MyBatis batch insert
        readEasyExeclMapper.saveDataBatch(list);
        list.clear();
    }

    private void insertByJdbc(List<UserInfo> list) {
        List<String> sqlList = new ArrayList<>();
        for (UserInfo u : list) {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.append("insert into ").append(u.getTableName())
                    .append(" ( UUID,ID,NAME,AGE,ADDRESS,PHONE,OP_TIME ) values ( ")
                    .append("'").append(ReadEasyExeclAsyncListener.uuid()).append("','")
                    .append(u.getId()).append("','")
                    .append(u.getName()).append("','")
                    .append(u.getAge()).append("','")
                    .append(u.getAddress()).append("','")
                    .append(u.getPhone()).append("',sysdate )");
            sqlList.add(sqlBuilder.toString());
        }
        JdbcUtil.executeDML(sqlList);
    }
}

UserInfo entity

@Data
public class UserInfo {
    private String tableName;
    private String uuid;
    @ExcelProperty(value = "ID")
    private String id;
    @ExcelProperty(value = "NAME")
    private String name;
    @ExcelProperty(value = "AGE")
    private String age;
    @ExcelProperty(value = "ADDRESS")
    private String address;
    @ExcelProperty(value = "PHONE")
    private String phone;
}