100M rows loading challenge

Intro

Most of us work with data on a daily basis. Sometimes it's few hundreds of rows, or even thousands and, speaking of business use cases, Excel is the first choice for most professionals in finance, insurance, banking, government, telecoms or other industries. This article goes beyond standard data usage patterns and tries to give new insights into how different apps handle loading data time when we increase the bar and scale it upwards. Surely, when you have 10k rows of data loaded in your favourite app, whether it is Excel, Power BI, SAS, Python or R it's lightning fast with modern computers today. Loading time measures in milliseconds so it is pretty much irrelevant what is your prefferable software to handle this task.

Creating the input file

Let's raise the bar and assume that we have 100M (that's 10^8) rows of data that we need to load into the memory (RAM) of our computer in order to prepare it for further analysis, cleaning, aggregation or reporting. For this purpose I created csv file with Python that contains one column without header (yeah, this additional row will really matter on 100M scale) :sarcasm, with integers ranging from 1 to 100M with increment (step) of 1. I think that all of you will agree this is the simplest task one could make, so let's try from this perspective.

In creating csv file I decided to use pandas Python library as it is the most common choice of Python data enthusiasts working with data structures. The same will be used for loading purposes as well. It took around 220 seconds to build csv file (zeroes.csv) that grew 943 MB in size on my SSD and will serve as an input file for our data loading benchmarks. My initial thought was to do the 1 billion row challenge, but gave up very soon. Why? You'll find out, just read on.

Hardware used is 6-core 12-threads AMD Ryzen 1600 running @ stock 3.2 GHz, 1x16 GB of DDR4 RAM @ 3000 MHz and 223 GB SSD (circa 500 MB/s read and write speed).

Contestants

There are four contestants in our case, Excel 2016, Power BI Desktop (Aug 2018 release), Python 3.6 and R 3.5.1. All apps are 64-bit versions running on 64-bit Windows 10 and 64-bit hardware already specified above.

Excel 2016

Let's kick off with Excel as widely used spreadsheet calculator. But, hey, how can you load 100M of rows of data in Excel spreadsheet when there is a limit of 1,048,576 (or 2^20) rows per sheet? Indeed, that is true. You can't load more that 2^20 rows of data in one sheet, but you can divide it into 100 sheets :sarcasm. Naaah, we will use Microsoft Power Pivot data model to store all data in Excel data model in just one table. What is Excel data model, never heard of it?! Unfortunately, 99.9% of Excel users never use Power Pivot & Power Query to handle huge amount of data, but we will :) So, how did we do it? If you have Excel 2016 there is a Data tab next to Formula tab and enables you to import text/csv files into the Excel data model. You can use older version of Excel (2013) as well. We will use option "Load to" and "Only Create Connection" while putting the check mark next to "Add this data to the Data Model". If we don't do so, Excel will try to load data into the existing sheet and will fail due to sheet row limitation we mentioned earlier.

I'm using localised version of Excel (Croatian language pack), so this strange language should not worry you, the position of option buttons and check mark is the same.

Few caveats before we start with the measurement. This article is not intended to be scientificly rigorous. But still, we'll try to be as accurate as possible when measuring loading times in different apps. Excel and Power BI do not have explicit time counters that give you the notion how long some action took in seconds or minutes, while others like Python and R handle this programatically. So, I used non-scientific approach (kill me now if you wish) in measuring the time from clicking on OK button and the finishing time in Excel by utilising stopwatch on my smartphone (that's why you need PhD). Later on, you'll see that this measurement error (max 2-3 seconds in Excel and Power BI) will not be crucial in determining the winner. Here are the Excel results and loading times for our 100M rows challenge:

If we convert this to seconds it is roughly 198 seconds. Not bad? We can't decide as we don't have peer comparison, yet.

Power BI Desktop

The biggest competitor to Excel in data analysis (excluding visualisation part) is Power BI. I will use its free (non-trial) version named Power BI Desktop made by the same company (Microsoft) that built Excel. Power BI under the hood also supports Power Pivot and Power Query features with some extra ingredients on the visualisation part which will not be relevant to us today. The procedure is almost the same as in Excel case, we'll import csv file with default settings not assuming anything regarding the data type. This non-biased data type approach is used in all test cases. And here are the results:

End time in Power BI Desktop resulted in approximately 165.2 seconds, which takes 16.6% less time (read it is faster) than in Excel case. I repeated experiment on both (Excel and Power BI) several times and the results were pretty much consistent with the first measurement. This was pretty surprise to me, because I had several positive experiences when using Excel vs. Power BI in terms of load speeds. But, let's face it, numbers are exact, so accept them as they are.

Python

Here things start to be more interesting. We are slowly turning to more scientific approach to measurement introducing computer timer (clock) and approaching this problem programmatically. In this benchmark I used Python 3.6 with Pandas library that all came together nicely packed in Anaconda package. Here's the code I used:

Interestingly enough, Python, or rather say, Pandas DataFrame loaded all the numbers in memory in 18.4 seconds. Was expecting to be very fast, but 9x times faster than the previous leader (Power BI) was truly a big surprise. You're probably wondering how much memory does it take when loaded, it goes from 3.7 GB to 4.4 GB when finished. Not so bad compared to Excel (3.5 GB to 6.4 GB) and Power BI (4.1 GB to 6.9).

And finally R

Did the same procedure with R (3.5.1) with the following code in R Studio:

What came as a huge surprise is the fact that results were very volatile and inconsistent. I shut down all necessary apps in the background every time I made new measurement. Let's look at the table:

So, on first load it took 99.3 seconds, then 59.6 seconds (probably in memory already) and the third time 65.2 sec. What is more surprising is the fact that memory consumption on the first run went from 4.1 GB to whooping 12.1 GB! In addition to that, it oscilated around 12 gigs for the second and third time which indicates that data was in-memory all the time. I'm not an expert in R, but there is some strange behaviour going there.

Colleague of my (@Tomislav Malis) pointed out that there is a package named readr that imports flat data in R super fast. Let's try with readr. First, install it in R Studio if not present by typing install.packages("readr") and slightly modify existing R code unbiased regarding the data type (integer):

The end result is much faster than with "stock" R code and is as follows:

Huge improvement over R baseline with 22.7 second! Let's look at the memory jump (3.9 GB to 4.6 GB). Nice! Let's marks this down.

Summary

Let's summarise the empirical part. I'll do it in a table in Excel :) Why? Because it is more convenient to me, faster and prettier in this particular case :)

The winner is, taaa-daaaa, Python with a small edge over R. It's not user friendly like Excel and Power BI, but will save your time in data loading and clicking. Some will say, it's not fair, you used Pandas and not standard built-in functions to load files. Or, the measurement is inaccurate as you used stopwatch. OK, fair enough, but these error margins still cannot affect the end result and we used the same optimisation on R side (readr).

If you want to benchmark your home machine (desktop, laptop) or cloud machine against the same numbers you can use the following files, just change the local path to csv file:

CSV input file, Python script, R script, R script (readr)

I would like to hear your input on this and, please, share and comment freely! Any ideas to test other apps?

UPDATE: Added readr R package.

Kindest regards,

Mislav