[FIXED] Peformance issues reading CSV files in a Java (Spring Boot) application

Issue

I am currently working on a spring based API which has to transform csv data and to expose them as json. it has to read big CSV files which will contain more than 500 columns and 2.5 millions lines each. I am not guaranteed to have the same header between files (each file can have a completly different header than another), so I have no way to create a dedicated class which would provide mapping with the CSV headers. Currently the api controller is calling a csv service which reads the CSV data using a BufferReader.

The code works fine on my local machine but it is very slow : it takes about 20 seconds to process 450 columns and 40 000 lines. To improve speed processing, I tried to implement multithreading with Callable(s) but I am not familiar with that kind of concept, so the implementation might be wrong.

Other than that the api is running out of heap memory when running on the server, I know that a solution would be to enhance the amount of available memory but I suspect that the replace() and split() operations on strings made in the Callable(s) are responsible for consuming a large amout of heap memory.

So I actually have several questions :

#1. How could I improve the speed of the CSV reading ?

#2. Is the multithread implementation with Callable correct ?

#3. How could I reduce the amount of heap memory used in the process ?

#4. Do you know of a different approach to split at comas and replace the double quotes in each CSV line ? Would StringBuilder be of any healp here ? What about StringTokenizer ?

Here below the CSV method

  public static final int NUMBER_OF_THREADS = 10;

   public static List<List<String>> readCsv(InputStream inputStream) {
            List<List<String>> rowList = new ArrayList<>();
            ExecutorService pool = Executors.newFixedThreadPool(NUMBER_OF_THREADS);
            List<Future<List<String>>> listOfFutures = new ArrayList<>();
            try {
                    BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream, StandardCharsets.UTF_8));
                    String line = null;
                    while ((line = reader.readLine()) != null) {
                            CallableLineReader callableLineReader = new CallableLineReader(line);
                            Future<List<String>> futureCounterResult = pool.submit(callableLineReader);
                            listOfFutures.add(futureCounterResult);
                    }
                    reader.close();
                    pool.shutdown();
            } catch (Exception e) {
                    //log Error reading csv file
            }

            for (Future<List<String>> future : listOfFutures) {
                    try {
                            List<String> row = future.get();
                    }
                    catch ( ExecutionException | InterruptedException e) {
                            //log Error CSV processing interrupted during execution
                    }
            }

            return rowList;
    }

And the Callable implementation

public class CallableLineReader implements Callable<List<String>>  {

        private final String line;

        public CallableLineReader(String line) {
                this.line = line;
        }

        @Override
        public List<String> call() throws Exception {
                return Arrays.asList(line.replace("\"", "").split(","));
        }
}

Solution

I don't think that splitting this work onto multiple threads is going to provide much improvement, and may in fact make the problem worse by consuming even more memory. The main problem is using too much heap memory, and the performance problem is likely to be due to excessive garbage collection when the remaining available heap is very small (but it's best to measure and profile to determine the exact cause of performance problems).

The memory consumption would be less from the replace and split operations, and more from the fact that the entire contents of the file need to be read into memory in this approach. Each line may not consume much memory, but multiplied by millions of lines, it all adds up.

If you have enough memory available on the machine to assign a heap size large enough to hold the entire contents, that will be the simplest solution, as it won't require changing the code.

Otherwise, the best way to deal with large amounts of data in a bounded amount of memory is to use a streaming approach. This means that each line of the file is processed and then passed directly to the output, without collecting all of the lines in memory in between. This will require changing the method signature to use a return type other than List. Assuming you are using Java 8 or later, the Stream API can be very helpful. You could rewrite the method like this:

public static Stream<List<String>> readCsv(InputStream inputStream) {
    BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream, StandardCharsets.UTF_8));
    return reader.lines().map(line -> Arrays.asList(line.replace("\"", "").split(",")));
}

Note that this throws unchecked exceptions in case of an I/O error.

This will read and transform each line of input as needed by the caller of the method, and will allow previous lines to be garbage collected if they are no longer referenced. This then requires that the caller of this method also consume the data line by line, which can be tricky when generating JSON. The JakartaEE JsonGenerator API offers one possible approach. If you need help with this part of it, please open a new question including details of how you're currently generating JSON.

Answered By - Tim Moore
Answer Checked By - Robin (JavaFixing Admin)