Fast and economic approaches for parsing large CSV files

Parsing large files is a recurring and challenging task. Right? It is too easy to write slow code that consumes a lot of memory.

As an example, let’s consider the following CSV file sample (the size of the real one is ~500MB)

userId,movieId,rating,timestamp
1,2,3.5,1112486027
1,29,3.5,1112484676
1,32,3.5,1112484819
1,47,3.5,1112484727
1,50,3.5,1112484580
1,112,3.5,1094785740
1,151,4.0,1094785734
1,223,4.0,1112485573
1,253,4.0,1112484940

Let’s assume that you need to compute the average rating for the ‘Braveheart’ movie (movieId=110). How would you implement it? Probably you would start with something like this:

var lines = File.ReadAllLines(filePath);
var sum = 0d;
var count = 0;

foreach (var line in lines)
{
    var parts = line.Split(',');

    if (parts[1] == "110")
    {
        sum += double.Parse(parts[2], CultureInfo.InvariantCulture);
        count++;
    }
}

Console.WriteLine($"Average rate for Braveheart is {sum/count} ({count} votes).");

The previous code is easy to read (that is good), but it is slow (took more than 6 seconds to run on my machine) and it consumes a lot of RAM (more than 2GB allocated processing a 500MB file).

The problem is we are loading all the data to the memory, putting a lot of pressure on the garbage collector. There is no need for doing that.

var sum = 0d;
var count = 0;
string line;

using (var fs = File.OpenRead(filePath))
using (var reader = new StreamReader(fs))
while ((line = reader.ReadLine()) != null)
{
    var parts = line.Split(',');

    if (parts[1] == "110")
    {
        sum += double.Parse(parts[2], CultureInfo.InvariantCulture);
        count++;
    }
}

Console.WriteLine($"Average rate for Braveheart is {sum / count} ({count} votes).");

This time we are loading data as we need and discarding it. This code is ~30% faster than the previous one, demands less memory (no more than 13MB for processing a 500MB file) and puts less pressure on the Garbage Collector (no more big objects nor objects that survive to the gen#0 collections).

Let’s try something different.

var sum = 0d;
var count = 0;
string line;

// Braveheart id movie id as span;
var lookingFor = "110".AsSpan();

using (var fs = File.OpenRead(filePath))
using (var reader = new StreamReader(fs))
while ((line = reader.ReadLine()) != null)
{
    // ignoring the voter id
    var span = line.AsSpan(line.IndexOf(',') + 1);

    // movieId
    var firstCommaPos = span.IndexOf(',');
    var movieId = span.Slice(0, firstCommaPos);
    if (!movieId.SequenceEqual(lookingFor)) continue;

    // rating
    span = span.Slice(firstCommaPos + 1);
    firstCommaPos = span.IndexOf(',');
    var rating = double.Parse(span.Slice(0, firstCommaPos), provider: CultureInfo.InvariantCulture);

    sum += rating;
    count++;
}

The primary goal of the previous code was to allocate fewer objects, to reduce the pressure on the garbage collector, getting better performance. Success! This code is 4x faster than the original one, consumes only 6MB and demands ~50% less garbage collector activations (Congrats, Microsoft!).

We are still allocating a string object for each line in the line. Let’s change it.

var sum = 0d;
var count = 0;

var lookingFor = Encoding.UTF8.GetBytes("110").AsSpan();
var rawBuffer =  new byte[1024*1024];
using (var fs = File.OpenRead(filePath))
{
    var bytesBuffered = 0;
    var bytesConsumed = 0;

    while (true)
    {
        var bytesRead = fs.Read(rawBuffer, bytesBuffered, rawBuffer.Length - bytesBuffered);

        if (bytesRead == 0) break;
        bytesBuffered += bytesRead;

        int linePosition;

        do
        {
            linePosition = Array.IndexOf(rawBuffer, (byte) '\n', bytesConsumed,
                bytesBuffered - bytesConsumed);

            if (linePosition >= 0)
            {
                var lineLength = linePosition - bytesConsumed;
                var line = new Span<byte>(rawBuffer, bytesConsumed, lineLength);
                bytesConsumed += lineLength + 1;


                // ignoring the voter id
                var span = line.Slice(line.IndexOf((byte)',') + 1);

                // movieId
                var firstCommaPos = span.IndexOf((byte)',');
                var movieId = span.Slice(0, firstCommaPos);
                if (!movieId.SequenceEqual(lookingFor)) continue;

                // rating
                span = span.Slice(firstCommaPos + 1);
                firstCommaPos = span.IndexOf((byte)',');
                var rating = double.Parse(Encoding.UTF8.GetString(span.Slice(0, firstCommaPos)), provider: CultureInfo.InvariantCulture);

                sum += rating;
                count++;
            }

        } while (linePosition >= 0 );

        Array.Copy(rawBuffer, bytesConsumed, rawBuffer, 0, (bytesBuffered - bytesConsumed));
        bytesBuffered -= bytesConsumed;
        bytesConsumed = 0;
    }
}

Console.WriteLine($"Average rate for Braveheart is {sum / count} ({count} votes).");

This time, we are loading the data in chunks of 1MB. The code seems a bit more complex (and it is). But, it runs almost 10x faster than the original one. Also, there are not enough allocations to activate the GC.

What do you think? How would you implement it? Share your thoughts in the comments.