Optimizing Large Excel Import Performance in Java Backend Applications

In a project requiring import of payment records from Excel, the original implementation using POI and row‑by‑row database queries could only handle a few dozen rows.

To handle 100k+ rows and insert over 300k records, the author iteratively optimized the import process across four versions:

Version 1: POI + manual List<ArrayList> mapping, per‑row validation and insertion, causing massive DB round‑trips.

Version 2: EasyPOI with cached house data in a HashMap via a custom SessionMapper and MyBatis ResultHandler, reducing repeated queries.

Version 3: Switched to Alibaba EasyExcel for faster reading, kept caching and batch insertion.

Version 4: Optimized batch insertion using MySQL values syntax, tuned batch size to 1000 rows, and employed parallel streams with a functional InsertConsumer utility to insert concurrently.

Key code examples include the custom SessionMapper, MapResultHandler, batch insert XML mapper, and the InsertConsumer class that splits a list into sub‑lists of size 1000 and processes them in parallel.

Additional performance tips: avoid excessive logging inside loops, use appropriate thread‑pool size, and prefer faster Excel libraries.

The final result achieved importing 100k rows, 420k arrears records, and related details in about 95 seconds.