Faster Excel Parsing in Ruby
Update: This post originally benchmarked against Xsv v0.3.2, but many performance changes (particularly to memory) were made, so I’ve now benchmarked against v0.3.7 and updated accordingly.
TL;DR: Xsv was ~5 times faster than alternatives at parsing the XLSX file I benchmarked it against, allocating the fewest objects and memory, making it by far the least resource-intensive of all benchmarked gems.
When exporting data for general use, we in the industry are likely to reach for CSV files; they’re basically plain-text, but with a sort-of agreed-upon structure — well, there is RFC 4180 but Wikipedia agrees that implementations are inconsistent at best.
For most people, though, Excel sheets are what’s used and understood. XLSX (aka OOXML) files have been the default file format in Microsoft Office for some years, replacing the proprietary XLS format of yore, and few will look beyond that. Excel sheets can contain considerably more complex information than CSVs, but due to their ubiquity and, perhaps, some level of ignorance, they’re commonly used to transfer simple collections of rows. Long story short: we’re tasked with offering Excel import options in our apps.
And yet, extracting simple data from XLSX files in Ruby is slow. The leading XLSX parser according to The Ruby Toolbox is rubyXL, which is not particularly fast at this task and can suck a ton of memory in the process. Now don’t get me wrong: rubyXL is awesome and can do a lot more than simply read XLSX files, but in most cases that’s all I need.
And my friend, Martijn, is in a similar situation: he just needs to parse simple user-uploaded XLSX sheets that users upload. So he wrote a gem optimized for parsing speed: Xsv. I thought the idea was really interesting, so I wrote some simple benchmarks for popular gems capable of parsing these files and tabulated the results below.
|Gem||Parses/second||Allocated Memory||Retained Memory||Allocated Objects||Retained Objects|
Benchmarks were run on on a 2018 15-inch MacBook Pro with a 2.6GHz 6-Core Intel Core i7, 16 GB 2400MHz memory and MacOS Catalina 10.15.3. Ruby is 2.7.0, whilst the gem versions are:
- rubyXL 3.4.12
- simple_xslx_reader 1.0.4
- creek 2.5.0
- roo 2.8.3
- Xsv 0.3.7
Spreadsheet can be found here, provided by the Dutch “Stichtse Vecht”.
I chose not to use the compare feature of benchmark-ips, instead opting to run each benchmark completely individually to ensure no side-effects.
The benchmark code can be found here. Though it’s not part of the codebase, I verified that each benchmark handles the same data by outputting the contents of each cell to a file and comparing the results via cmp.
If you find any errors, please make an issue or a PR.
Speed-wise, the benchmarks speak for themselves: Xsv is ~5 times faster than alternatives.
Retained: long lived memory use and object count retained due to the execution of the code block.
Allocated: All object allocation and memory allocation during code block.
Suffice to say, Xsv wins on all fronts with regards to raw performance.
Don’t forget that Xsv is only there to parse sheets; for writing and more advanced functionality, you’ll still need to reach for another tool.
Suggestions to improve Xsv are welcome.