Phoenix json API extremely slow when serving CSV files

Maybe try nimble_csv. It has NimbleCSV.RFC4180 build in, which should parse your file.

"path/to/file"
|> File.read!() 
|> NimbleCSV.RFC4180.parse_string()

seems also faster than streaming the file (at least for ~20MB sized files).

The only downside is, that parse_string/1 returns a list of lists and you have to map it to a map yourself.

I see two possible solutions. First, as @moogle19 suggested, you can use NumbleCSV’s parse_stream and send a chunked response using Plug.Conn.send_chunked/2, never loading the whole file into memory. Second, you can, again, use parse_stream to stream the contents into a temporary file and use Plug.Conn.send_file/5. I believe the second one is trickier because in case you have a lot of requests there should be rate limiting in order to not fill your disk memory.

Can you measure which code is slow ? Is it the parsing, the JSON encoding, etc.

Also you could:

• use Stream.map instead of Enum.map to avoid keeping too much data in memory
• use Jason.Fragment on each in Stream.map to encode to JSON line by line
• use Jason.encode_to_iodata instead of Jason.encode to avoid converting the full JSON structure to a string before sending it
• convert your files to JSON once and serve the JSON files instead of converting from CSV for every request

Can you measure which code is slow ? Is it the parsing, the JSON encoding, etc.

Good idea, to find out which is the “culprit” and isolate the problem.
But I don’t know how to do that.

When input data is expected to be large, always use streaming and never keep all of it in memory.

You can use Benchee…

I tested it with a ~130MB CSV file and the default benchee config:

Name             ips        average  deviation         median         99th %
nimble         0.121         8.24 s     ±0.00%         8.24 s         8.24 s
csv           0.0173        57.91 s     ±0.00%        57.91 s        57.91 s

Code is:

file = "samples/large.csv"

Benchee.run(%{
  "nimble" => fn ->
    file
    |> File.stream!()
    |> NimbleCSV.RFC4180.parse_stream()
    |> Stream.map(fn [first, second, third, fourth, fifth] -> %{f: first, s: second, t: third, fo: fourth, fi: fifth} end)
    |> Enum.into([])
  end,
  "csv" => fn ->
    file 
    |> File.stream!() 
    |> CSV.decode!() 
    |> Enum.into([])
  end
})

It seems nimble_csv is much faster for large files.

You can wrap each part of the code with :timer.tc/1:

{decode_µs, data} = :timer.tc(fn -> decode_from_csv(file) end)
{encode_µs, json} = :timer.tc(fn -> Jason.encode_to_iolist!(data) end)

etc.

Thank you all, I managed to do this implementation using NimbleCSV:

 defp csv(p) do

    [header | data] = p
      |> Path.expand(__DIR__)
      |> File.stream!()
      |> NimbleCSV.RFC4180.parse_stream(skip_headers: false)
      |> Enum.into([])

    Enum.map(data, fn x -> Enum.zip(header, x) |> Map.new() end)
 end

It does the job but not much improvement
3.5MB → 22.46s
21.4MB → 7.2min (tested this file on a similar API, written long time ago, in php5, calling a humble fgetcsv() with no streams and we got 22.22s )

So still far for being usable.
I presume the issue is not streaming in the csv file but the second part - assembling the map and sending out the json.

Is there a reason to stream the data into memory? If not try using the non streamed API, which should be faster.