Sending excel file as response is slow

General Usage Performance
, ,
4

Thanks for the example data, I did some profiling with StatProfilerHTML.jl and for me, about 80% of the time is spent calling ZipFile.jl/src/ZipFile.jl at a599b0aac5d17403fdbbc4ea63612be299cf8417 · fhs/ZipFile.jl · GitHub
So this seems to be the same issue you were having in File zipping taking longer for large files

Here is what I profiled:

using CSV
using DataFrames
using XLSX
using StatProfilerHTML

function gen_fake_csv(path::String, nlines::Int)
    header = "REPORT_DT,COMPANY_ID,NUM,DPRT,DPRT_TML,ARRV,ARRV_TML,MARKET,ENTITY,HUB,SUBFT,FLT,DEPS,BHRS,MILES,CAP,MILLIONS,SOURCE,YYYYMM,REPORT_YYYYMM,categ"
    line = "7/1/2024,XY,1,ABC,1234,ABC,1234,ABCDFE,XYZ,ABC,12A,789,1,16.3333,9876,456,2.16959,7/1/2024,202407,202407,787"
    open(path; write=true) do f
        println(f, header)
        join(f, Iterators.repeated(line, nlines), "\n")
    end
end

function create_excel_file(df)
    # Create an in-memory buffer for the Excel file
    io = IOBuffer()
    XLSX.openxlsx(io, mode="w") do xf
        sheet = xf[1]
        XLSX.writetable!(sheet, collect(DataFrames.eachcol(df)), DataFrames.names(df))
    end
    return take!(io)
end

test_file = "test.csv"
gen_fake_csv(test_file, 100000)
df = CSV.read(test_file, DataFrame)
@time create_excel_file(df) # compile the method
@profilehtml create_excel_file(df) # profile