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.

Compartilhe este insight:

4 respostas

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Elemar Júnior

Sou fundador e CEO da EximiaCo e atuo como tech trusted advisor ajudando diversas empresas a gerar mais resultados através da tecnologia.

Elemar Júnior

Sou fundador e CEO da EximiaCo e atuo como tech trusted advisor ajudando diversas empresas a gerar mais resultados através da tecnologia.

Mais insights para o seu negócio

Veja mais alguns estudos e reflexões que podem gerar alguns insights para o seu negócio:

Quando estamos desenvolvendo aplicações distribuídas, não devemos nos perguntar se teremos problemas de conectividade. No lugar disso, devemos nos perguntar...
Compete ao arquiteto corporativo a responsabilidade de descrever, analisar e comunicar os diversos aspectos da arquitetura corporativa, bem como registrar...
A pergunta do título, embora simples, não é fácil de responder. De qualquer forma, o conhecimento necessário para respondê-la é...
Nessa semana, em uma dessas conversas que valem a pena, surgiu uma breve discussão sobre um texto antigo de Rubem...
No meu cotidiano, reconheço que, por mais estranho que pareça, comprometo muito do meu tempo ouvindo música ruim até que,...
Já está disponível o registro da conversa com os meninos da Lambda3, meus amigos Giovanni Bassi e Victor Cavalcante, sobre...