readcsv performance

[ViralBShah]

I am loading a 100MB csv file using readcsv, and it takes 70 seconds. The file reads comma separated values that are a mix of integers and strings. It is 1,600,000 rows and 9 columns. Some rows have a 10th column as well, but that just becomes part of the last column.

The profiler reveals that a majority of the time is spent in split, which is not unexpected, but it would be nice to load such files quickly.

[ViralBShah]

Cc: @tanmaykm

[tanmaykm]

I noticed that countlines uses a local 8K buffer to read data into and works off that. That seems to add to its speed. Should that be adopted in readuntil also?

Another approach could also probably be to have a CSV data type that just keeps the data as a blob and cells as offsets into it. Then getindex can return strings (or any other type) on the fly. This should work well for cases where the CSV is readonly or where the CSV needs to be filtered through a user supplied function.

[tanmaykm]

related: https://groups.google.com/forum/?fromgroups=#!topic/julia-users/fej63DPHgYo

[ViralBShah]

@loladiro Should readcsv be rewritten to use a buffer like countlines? Roughly when do you plan to have buffered I/O implemented?

[ViralBShah]

Also, I believe that buffered I/O will only help address about 20% of the performance issue here.

[JeffBezanson]

File reading still uses the old I/O system, which is already buffered.

[ViralBShah]

Ok, this is what readdlm is doing. It turns out that if instead you do reads in 8k blocks, the way countlines does, the time is much lesser.

julia> @time while !eof(f)
         r = readuntil(f, "\n")            
       end
elapsed time: 24.508526186 seconds

[ViralBShah]

This may not have to do with buffered I/O, but just the sheer number of read calls and the number strings created. However, reading a buffer, and then working line by line within that buffer does seem to be much faster.

[JeffBezanson]

We need to skip the encoding check that readline does. Somehow we need to specify the encoding of the file.

[tanmaykm]

Neither byte_string_classify nor u8_isvalid appear that prominently in sprofile. Will create a readline_ascii and try this out though.

[tanmaykm]

A readcsv_ascii method that avoids the call to byte_string_classify in readuntil seems only slightly faster, not enough to make a difference.

julia> @time csv = Base.readcsv("vList1.csv");
elapsed time: 194.58129686 seconds

julia> @time csv = Base.readcsv_ascii("vList1.csv");
elapsed time: 186.778873825 seconds

[ViralBShah]

With @tanmaykm 's readcsv implementation, I am seeing 5x performance gains on large files. The trick seems to be avoiding the use of split. I tried read.csv in R as well. Here are some timings for a csv file that is 347M and has 1.8M rows and 12 columns.

                           Time  Memory in top
R (read.csv)               400s   5.5G
Julia (current readcsv)    197s   2.2G
Julia (new readcsv)         37s   2.2G

[johnmyleswhite]

Comparing our readcsv with R's is a little problematic: R's function does much more work. Our readcsv is basically broken by design since it will only parse a trivial subset of CSV files that only contain numbers and nothing else.

The DataFrames read_table function is a better comparison. It's unbearably slow on a file of that size.

[ViralBShah]

R's readcsv has a ton of features, which I expect we will want too. This was just to check that we are not grossly slow or inefficient in other ways. Currently, all the time in our readcsv seems to be going in memory allocation of substring objects even in the new implementation - but the performance improvement is nice to have.

I did try read_table at first, but that barfed on my file. How do we get these improvements into read_table? Eventually, I do want a dataframe. For now, I am using readcsv and passing the array to DataFrame().

[johnmyleswhite]

The performance improvements are huge. Anything @tanmaykm can do to make IO faster will be a big gain in the future.

@JeffBezanson and I were chatting a while back about readcsv in Base: it's tempting to just remove it and have people call readdlm since that's all we're doing. I think the deciding argument for me was this point: if you're trying to read a table from Excel via CSV, you probably want a DataFrame as the result, not an array of strings or anything else that Base could provide.

I will try to get back to read_table next week since it's clearly the weakest part of the DataFrames package. I got sidetracked by the amount of bugs I kept finding in Distributions, which has kept me busy recently. I believe that we need more aggressive buffering in read_table to make it functional. I'll probably need help figuring out how to get read_table to be fast enough.