Program architecture for handling large .csv files

5 views (last 30 days)
Robert Scott
Robert Scott on 27 Jul 2021
Commented: Rena Berman on 23 Sep 2021
This question was flagged by Jeremy Hughes
Good Afternoon
So i am a bit concerned. I just spent a week writing a matlab program that takes in a large amount of data from a .csv file
When i started the project i opted for a table variable type because of its ease of use with functions like readtable()
However i am not stuck with an unusable program. It takes hours for matlab to process my .csv files
I have considered the following options
it appears matlab has not yet developed any performance for the table datatype with no hope in sight.
1: re-write the entire program using arrays
Problem, the data in my file is all hex values. I will need to convert them to decimal numbers using the hex2dec function which only works with a char data type. So using an array of doubles is out of the question. Not sure where to go here
2: try to re-write the program using paraelle toolbox

Answers (5)

Peter Perkins
Peter Perkins on 28 Jul 2021
Edited: Peter Perkins on 28 Jul 2021
With no code at all to go on, it's pretty hard to give specific advice.
Hex issues aside, the first advice I would give would be to write vectorized code. The fact that you have 50k calls to subscripting suggests that you are doing scalar operations in a tight loop. That's not the best way to write code in MATLAB. Again, not much information to go on, so hard to say. So many calls to brace subscripting suggests that you are doing assignments to one variable using braces, which, as Walter points out, is slower than using dot (because it has to do a lot more in general). That's an unfortunate difference that is not highlighted in the doc, but perhaps should be. In any case, no code to go on, so ...
Walter is correct that since 18b, performance, especially for assignments into large tables, has improved quite a bit.
If you can't vectorize, and you can't upgrade to a newer version, it's probably not necessary to "re-write the entire program using arrays". It's usually possible to focus only on the tight loop and "hoist" some of the variables out of the table for that part of the code, then put them back in. Use tables for the organization and convenience they provide, use raw numeric in small doses for performance. Tables have a lot going on, and will never be as fast as double arrays. That doesn't mean you should avoid them.
It really helps to provide concrete code examples.
Walter Roberson
Walter Roberson on 28 Jul 2021
It is difficult to discuss high level program architecture without some understanding of the kinds of operations that need to be performed.
For example there are some needs for which the most efficient method would be to use a multidimensional hypercube of characters, with text for any one "word" stored as columns (instead of the typical rows.) But there are other needs for which you might want the "words" stored as rows in a hypercube.

Sign in to comment.

Walter Roberson
Walter Roberson on 27 Jul 2021
In some cases, depending on the format of the file, you have some options of how to proceed:
  • readtable() and readmatrix() and readcell() all permit a 'Format' option, using the same format specifications as are are used by textscan() -- including the potential to use the %x format (possibly with a length specification, if your fields are fixed width.)
  • you could use textscan() directly, since you are working with text files
  • you could use lower-level I/O commands, including fscanf() or fgetl() with sscanf(), depending how complicated your files are. If your format is complicated enough that you effectively need to read one line at a time, then this might be lower performance
  • When your file is not super-complicated but does have different sections, then there are surprisingly high performance gains to be had by reading the entire file in as text and using regexp() to break it up into subsections and then textscan() or sscanf() the subsections. Performance gains relative to looping testing each line, that is.
Robert Scott
Robert Scott on 28 Jul 2021
Thanks for your comments. Could possibly give that a shot

Sign in to comment.

Jeremy Hughes
Jeremy Hughes on 28 Jul 2021
Edited: Jeremy Hughes on 29 Jul 2021
First, it would help to see an example file, and some sample code that demonstrates the problem.
Here's the best I can say with what I see.
If the entire variable is in hex format, you can use import options to do that conversion quickly on import.
detectImportOptions will see 0x1a as a hex value, but not without the prefix, but it can be read as hex if you ask for it.
opts = detectImportOptions(filename,"Delimiter",",")
% varNamesOrNumbers = [1 3 5]
% or varNamesOrNumbers = ["Var1","Var3"]
opts = setvaropts(opts, varNamesOrNumbers, "NumberSystem", "hex", "type", "auto");
% You can also improve reading performance by selecting only the columns
% you would like (this is optional)
opts.SelectedVariableNames = opts.VariableNames([1 3 5 7 9]);
T = readtable(filename,opts)
dpb on 31 Jul 2021
If i can bring it in ahead of time as a hex value i will be in much better shape. Can one of you offer a suggestions?"
This has already been done above by Walter.
NB: readXXX of the new forms like readmatrix all pass off the heavy lifting to readtable in the end; the others are just front ends that give it some klews of what the content of the file is and specify how the data are to be returned to the caller.
These may provide a small performance boost over readtable; what they mostly do is return an array or cell array instead of a table. If all operations are on an array basis, then there's probably no advantage at all in using tables.
However, using detectImportOptions and customizing an import object including the variable types as hex where they are will almost certainly give benefits -- the primary one being importing the data as the decimal value instead of 'char' or cellstr or strings which will have to then convert -- instead, pass the work off to the system i/o library.

Sign in to comment.

Robert Scott
Robert Scott on 3 Aug 2021
I want to get back to this thead.
So i have a significant time investment in this project and wanted to thank everyone that chimed in.
I have found my own answer and im really surprised no one mentioned this that i can see.
The answer is, you cant!!! you cant read in big files. The way matlab brings these files into ram has to much overhead. I rewrote my program with every trick in the book and managed nearly a 1000X speed increase from my rev 1! Well guess what! it still wasnt good enough. When i started to ask my program that was now blazing fast with all kinds of tricks, vectorizing, parfor loops etc to run a 10k line file i got wonderful speeds. 500uS a line! it was excellent. Well guess what happens when you read in a file that has 500k lines, my 500us is now 500mS. The answer is you just cant. You cant bring in all the data locally and not impact performance. You need to read it one line at a time OUTSIDE of matlab so matlab doesnt slow down.
Here is the answer, which is the point of this post. The answer is to use fopen and fgets. fgets is pretty fast.
read the file line by line as you parse it. That is the only way. This also keeps from over filling your ram.
I hope my pain and suffereing may help someone in the future. Dont even bother with tall arrays and datastores. All of this stuff has way to much overhead and what is meant as a solution is actual your demise.
fopen and fgets and read it line by line. fgets executes on a uS scale and never gets slowed down by ram.
What a waste of 10 days this has been for me. Thanks to everyone that supported this learning experiment.
dpb on 3 Aug 2021
"... each line consists of repeated entries that are 4 hex digits representing signed 16 bit numbers..." @Walter Roberson
I finally got a response that all are hex per record except for the first and last elements of each record, but never responded as to what those two are. All the other details are still opaque given refusal to provide any further useful details...

Sign in to comment.

Robert Scott
Robert Scott on 3 Aug 2021
This case is closed. I have thanked you all for your help and i am not able to share or discuss this any further. I have now said numerous times i am unable to share ANYTHING other then general matlab prinicipals and discussing how matlab itself works in general terms. I have found a suitable solution using fgets. Good day to you all.




Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!