For the prior benchmarks I used csv files from here: https://www.stats.govt.nz/large-datasets/csv-files-for-download/
Geographic units, by industry and statistical area: 2000–2021 descending order – CSV is ~ 120MB when unzipped.
But I am not sure how the number of columns affects the runtime. The example has only 5 columns.
I used the 10000_sales.csv file from NimbleCsv, I duplicated the content until the total size was 20M (19.2 Mio). It has 15 columns.
Now with this code using chunks it takes 2.8 seconds with wget to download the JSON data (size is 50M), although in the browser it loads forever.
defmodule XsvWeb.PageController do
use XsvWeb, :controller
alias NimbleCSV.RFC4180, as: CSV
def index(conn, _params) do
conn = send_chunked(conn, 200)
send_csv(conn, "big_sales.csv")
end
defp send_csv(conn, file) do
csv_stream = csv_stream(file)
{:ok, conn} = chunk(conn, "[")
conn =
Enum.reduce_while(csv_stream, conn, fn chunk, conn ->
case Plug.Conn.chunk(conn, chunk) do
{:ok, conn} ->
{:cont, conn}
{:error, :closed} ->
{:halt, conn}
end
end)
{:ok, conn} = chunk(conn, "]")
conn
end
defp csv_stream(file) do
headers = headers(file)
headers |> IO.inspect(label: ~S[headers])
stream =
file
|> File.stream!()
|> CSV.parse_stream(skip_headers: true)
stream
|> Stream.take(1)
|> Stream.map(fn x -> Enum.zip(headers, x) |> Map.new() |> Jason.encode_to_iodata!() end)
|> Stream.concat(
stream
|> Stream.drop(1)
|> Stream.map(fn x ->
[?,, Enum.zip(headers, x) |> Map.new() |> Jason.encode_to_iodata!()]
end)
)
end
defp headers(file) do
file
|> File.stream!()
|> Stream.take(1)
|> CSV.parse_stream(skip_headers: false)
|> Enum.at(0)
end
end
Note that the browser makes it slow to display but with fetch("http://localhost:4000/").then(x => x.json()).then(x => console.log('x', x)) it is still less than 4 seconds for 54.8 MB of JSON.
Now @quda I know it is not the original problem but converting to JSON makes the final file size more than twice as big. Is it possible to just send the CSV file? or JSON arrays instead of objects?
That being said I do not think it is possible to beat something as simple as basic PHP like you said:
<?php
$csvFile = file('big_sales.csv');
$data = [];
foreach ($csvFile as $i => $line) {
if ($i == 0) {
$headers = str_getcsv($line);
} else {
$row = str_getcsv($line);
$data[] = array_combine($headers, $row);
}
}
echo json_encode($data, JSON_PRETTY_PRINT);
@lud you’re right about (not) beating php.
Very disappointed.
We were asked to migrate/refactory the old backend for APIs, developed 7 years ago on php5/nginx because it recently started to have performance issues: too many concurrent calls from the downstream API consumers. The cpu and memory were always at 100% on this server.
The idea of our IT solution architect was to switch to the Elixir/Phoenix stack for it’s performance using concurrent connections.
But it seem that this stack is far for being fast on processing individual requests… 
Honestly, I couldn’t imagine there it can be something worse than php in terms of performance.
EDIT: @lud’s code is more complete than mine, I only demonstrated how to avoid passing huge collections around.
Seconded, without that we can just theorize until the end of time.
I agree with @LostKobrakai that memory is likely not a problem but I disagree it should be the default solution. You can use streaming with a big buffer and get the best of both words: low rate of hitting your storage + predictable memory usage.
@quda Here is a complete example on how I would do it:
def for_each_csv_row(path, processor) when is_function(processor, 1) do
path
|> Path.expand(__DIR__) # or `Path.absname` if you need that.
|> File.stream!([], 256 * 1024) # use a 256KB streaming buffer.
|> NimbleCSV.RFC4180.parse_stream(skip_headers: false)
|> Enum.map(processor) # --- FUNCTIONAL PROGRAMMING, HELL YEAH! ---
# or you can use `Enum.each` if you don't want to convert the values
# and only need the side effects.
end
It is absolutely meaningless to utilize optimal methods for accessing data if you just end up assigning it to a huge variable in the end and return it from a function. It’s best if you pass a function that operates on a single record instead. That way the full collection of original elements will never be in the memory and the runtime won’t have to copy it around – you’ll only get the converted collection in the end. (Or if you only need side effects you won’t even have the resulting collection in memory as well.)
I find Elixir and Phoenix a joy to work with. Individual requests are generally very fast for common purposes. But here you are doing a lot of string manipulations and iterating over large chunks of data, witch is not what Elixir is designed to do fast, unfortunately.
Honestly, I couldn’t imagine there it can be something worse than php in terms of performance.
I have been a long time user of PHP and I am surprised of your comment. PHP uses a lot of C code and is quite fast for many things actually. Lots of PHP libraries are bloated with overengineered, convoluted, interface-heavy APIs but the raw performance is there (to my taste at least).
Now, If you just need to serve static CSV files that are just sitting in the app directory, why not just write the JSON version in the same directory at the same time and just serve that?
How much Elixir did you learn before coming to the forum? Do you know the basics of functional programming?
I personally prefer to give people good advice after they have done some homework and not before. Still, let me add to my example so it is at least complete:
#
# The function signature `[first, second, third, fourth, fifth]` is just an example
# for when your CSV records have five fields: first name, last name, age,
# employee position and salary.
#
processor = fn [first, second, third, fourth, fifth] ->
%{
first_name: first,
last_name: second,
age: third,
position: fourth,
salary: fifth
}
end
Passing that to the function I gave you above will yield a list of maps in the shape visible in the processor function.
Exactly ! This php backend became an unmanageable beast in the last years (thousands of internal commits made by people are not working anymore in this project), that’s why it was decided to refactory it using a better stack. Slim, less code, better manageability. And better performance on concurrent calls.
Unfortunately, it seems Elixir/Phoenix is not suitable for this.
We will look forward to alternatives.
My suggestion is to use @lud 's solution to see if it is good enough or not.
If you still want more speed, you can write a native csv to json converter in a command line tool using rust or go, reading from the FS and writing to stdout. That should be doable in 100 LOC. Then use a port to run the converter and stream the chunked results to HTTP. That should be trivial in Elixir. I believe you can achieve line speed in this case.
You could have explained this from the beginning.
It does not matter my (humble) experience on Elixir. I was assigned to do this task, given a deadline and I have to do it. That’s why I appealed to this forum. I thought here members are supposed to help people like me not (only) experts in Elixir.
No offence, though. 