R data table: strangely poor performance in subsetting

user_3915051 0
Viewed 91

I was under the impression that data.table is extremely well optimized, so I was quite surprised to see this:

library(data.table)

SimData <- data.table(ID = sample(1:4e5, 4e6, replace = TRUE),
                      DATE = sample(seq(as.Date("2000-01-01"), as.Date("2019-12-31"), by = "day"),
                                    4e6, replace = TRUE))

microbenchmark::microbenchmark(SimData[ID==1&DATE>="2005-01-01"])
microbenchmark::microbenchmark(SimData[ID==1][DATE>="2005-01-01"])

The two solutions are quite obviously the same, yet there is more than an order of magnitude difference in runtime. Is it possible that data.table performs so poorly with the first form? (I.e., that it can't automatically optimize this call.) Or I overlook something here...?

data.table r optimization
2 Answers

The long operation is SimData[DATE>="2005-01-01"] because it returns millions of rows.

microbenchmark::microbenchmark(SimData[DATE>="2005-01-01"],SimData[ID==1])
Unit: microseconds
                          expr     min       lq     mean   median       uq      max neval
 SimData[DATE >= "2005-01-01"] 32542.8 44549.55 51323.53 47529.75 50258.10 117396.3   100
              SimData[ID == 1]   820.0  1043.55  1397.79  1435.15  1688.25   2302.5   100

SimData[ID == 1] is much shorter as it returns only a few rows,

When you execute SimData[ID==1&DATE>="2005-01-01"], you force both evaluations on all rows.

With SimData[ID==1][DATE>="2005-01-01"] the quick operation is done first, and the subsequent filter is also quick because applied on only a few row.

As mentioned by @jangorecki, there is a room for improvement in that matter.

user_135133280

data.table optimizes the queries like X==x, and ID==1 is in this form thus the first time you run this query it takes a while but after that calling the same query is very fast. In your case second run of the query SimData[ID==1] is very fast and the returned data table is very small which makes the second query also fast.

user_178362380
Related