Benchmarks

basetable runs every operation on its own bundled C++ engine and has no data.table dependency. This vignette measures it head to head with data.table, dplyr and collapse using the bench package, which reports both wall-clock time and the memory allocated by each expression.

set.seed(1)
n <- N
d <- data.frame(
  g  = sprintf("k%07d", sample(2000L, n, replace = TRUE)),               # ~2000 groups
  gh = sprintf("k%08d", sample(max(n %/% 10L, 1L), n, replace = TRUE)),  # ~n/10 groups
  x  = rnorm(n),
  y  = rnorm(n),
  id = seq_len(n),
  stringsAsFactors = FALSE
)
dim_tbl <- d[!duplicated(d$g), c("g", "y")]

if (HAVE_DT) {
  dt  <- data.table::as.data.table(d)
  dmt <- data.table::as.data.table(dim_tbl)
}

Running the benchmark

Each operation is timed for every engine with bench::mark(..., check = FALSE, memory = TRUE). check = FALSE because the engines return different object types for the same logical result.

bench_one <- function(label, exprs) {
  keep <- c(TRUE, HAVE_DT, HAVE_DP, HAVE_CL)[seq_along(exprs)]
  exprs <- exprs[keep]
  m <- bench::mark(exprs = exprs, iterations = REPS, check = FALSE, memory = HAVE_PROFMEM)
  m$operation <- label
  m$engine    <- names(exprs)
  m
}

marks <- list(
  bench_one("filter", list(
    basetable  = quote(basetable::subset(d, x > 0.5)),
    data.table = quote(dt[x > 0.5]),
    dplyr      = quote(dplyr::filter(d, x > 0.5)),
    collapse   = quote(collapse::fsubset(d, x > 0.5)))),
  bench_one("sort (string key)", list(
    basetable  = quote(basetable::orderrows(d, by = c("g", "x"))),
    data.table = quote(data.table::setorder(data.table::copy(dt), g, x)),
    dplyr      = quote(dplyr::arrange(d, g, x)),
    collapse   = quote(collapse::roworderv(d, c("g", "x"))))),
  bench_one("distinct", list(
    basetable  = quote(basetable::uniquerows(d, cols = "g")),
    data.table = quote(unique(dt[, list(g)])),
    dplyr      = quote(dplyr::distinct(d, g)),
    collapse   = quote(collapse::funique(collapse::get_vars(d, "g"))))),
  bench_one("count by group", list(
    basetable  = quote(basetable::count(d, by = "gh", sort = FALSE)),
    data.table = quote(dt[, .N, by = gh]),
    dplyr      = quote(dplyr::count(d, gh)),
    collapse   = quote(collapse::fcount(d, gh)))),
  bench_one("sd by group", list(
    basetable  = quote(basetable::aggregate(d, by = "g", value = "x", fun = sd, sort = FALSE)),
    data.table = quote(dt[, list(x = sd(x)), by = g]),
    dplyr      = quote(dplyr::summarise(dplyr::group_by(d, g), x = sd(x), .groups = "drop")),
    collapse   = quote(collapse::collap(d, x ~ g, collapse::fsd, sort = FALSE)))),
  bench_one("equi join", list(
    # basetable::merge() keeps input order; pin data.table to sort = FALSE so
    # neither side also sorts the joined result.
    basetable  = quote(basetable::merge(d, dim_tbl, by = "g")),
    data.table = quote(merge(dt, dmt, by = "g", sort = FALSE)),
    dplyr      = quote(dplyr::inner_join(d, dim_tbl, by = "g")),
    collapse   = quote(collapse::join(d, dim_tbl, on = "g", how = "inner", verbose = 0)))),
  bench_one("semi join", list(
    basetable  = quote(basetable::semimerge(d, dim_tbl, by = "g")),
    data.table = quote(dt[dmt, on = "g", nomatch = NULL]),
    dplyr      = quote(dplyr::semi_join(d, dim_tbl, by = "g")),
    collapse   = quote(collapse::join(d, dim_tbl, on = "g", how = "semi", verbose = 0))))
)

ops <- c("filter", "sort (string key)", "distinct", "count by group",
         "sd by group", "equi join", "semi join")

res <- do.call(rbind, lapply(marks, function(m) {
  data.frame(
    operation = m$operation,
    engine    = m$engine,
    median_ms = as.numeric(m$median) * 1000,
    mem_mb    = as.numeric(m$mem_alloc) / 1024^2,
    itr_sec   = as.numeric(m$`itr/sec`),
    stringsAsFactors = FALSE
  )
}))
res$operation <- factor(res$operation, levels = ops)
res$engine    <- factor(res$engine, levels = c("basetable", "data.table", "dplyr", "collapse"))

A raw `bench::mark` result

This is the object bench returns, for the grouped-sd case: iterations per second, memory allocated, and garbage collections.

marks[[which(ops == "sd by group")]][, c("engine", "min", "median", "itr/sec", "mem_alloc", "n_gc")]

## # A tibble: 4 × 5
##   engine          min   median `itr/sec` mem_alloc
##   <chr>      <bch:tm> <bch:tm>     <dbl> <bch:byt>
## 1 basetable    2.88ms   2.96ms     336.    77.28KB
## 2 data.table   8.34ms   8.79ms     114.      8.2MB
## 3 dplyr       39.13ms  39.53ms      24.8   11.31MB
## 4 collapse     5.42ms    5.5ms     181.     1.58MB

Summary table

median (ms) and mem (MB) are lower-is-better; vs basetable is the engine’s median time divided by basetable’s for that operation (below 1 means faster than basetable).

bt <- res[res$engine == "basetable", c("operation", "median_ms", "mem_mb")]
names(bt)[2:3] <- c("bt_ms", "bt_mb")
tab <- merge(res, bt, by = "operation")
tab$vs_time <- tab$median_ms / tab$bt_ms
tab$vs_mem  <- tab$mem_mb / tab$bt_mb
cols <- if (HAVE_PROFMEM) {
  c("operation", "engine", "median_ms", "mem_mb", "vs_time")
} else {
  c("operation", "engine", "median_ms", "vs_time")
}
tab <- tab[order(tab$operation, tab$engine), cols]
for (col in intersect(c("median_ms", "mem_mb", "vs_time"), names(tab)))
  tab[[col]] <- format(round(tab[[col]], 2), nsmall = 2)
nms <- if (HAVE_PROFMEM) {
  c("Operation", "Engine", "Median (ms)", "Mem (MB)", "vs basetable")
} else {
  c("Operation", "Engine", "Median (ms)", "vs basetable")
}
knitr::kable(
  tab, row.names = FALSE,
  col.names = nms,
  align = c("l", "l", "r", "r", "r")[seq_along(nms)]
)

Operation	Engine	Median (ms)	Mem (MB)	vs basetable
filter	basetable	1.07	4.43	1.00
filter	data.table	3.05	7.64	2.84
filter	dplyr	4.33	9.43	4.04
filter	collapse	1.94	5.86	1.81
sort (string key)	basetable	13.27	10.34	1.00
sort (string key)	data.table	38.43	14.13	2.90
sort (string key)	dplyr	35.25	20.85	2.66
sort (string key)	collapse	18.18	11.52	1.37
distinct	basetable	0.81	0.03	1.00
distinct	data.table	6.70	6.24	8.27
distinct	dplyr	2.77	5.35	3.42
distinct	collapse	0.84	1.25	1.04
count by group	basetable	1.65	0.36	1.00
count by group	data.table	12.85	9.11	7.76
count by group	dplyr	286.09	9.95	172.87
count by group	collapse	2.28	1.75	1.38
sd by group	basetable	2.96	0.08	1.00
sd by group	data.table	8.79	8.20	2.98
sd by group	dplyr	39.53	11.31	13.38
sd by group	collapse	5.50	1.58	1.86
equi join	basetable	1.73	2.30	1.00
equi join	data.table	1.57	2.29	0.91
equi join	dplyr	23.55	30.99	13.61
equi join	collapse	1.04	3.74	0.60
semi join	basetable	1.26	1.17	1.00
semi join	data.table	14.84	17.72	11.75
semi join	dplyr	20.72	24.83	16.41
semi join	collapse	0.85	1.14	0.67

Speed

ggplot(res, aes(engine, median_ms, fill = engine)) +
  geom_col(width = 0.7) +
  geom_text(aes(label = round(median_ms)), hjust = -0.15, size = 3) +
  facet_wrap(~operation, ncol = 2, scales = "free_x") +
  coord_flip() +
  scale_fill_manual(values = c(basetable = "#1b7837", data.table = "#762a83",
                               dplyr = "#c2a5cf", collapse = "#e08214")) +
  labs(x = NULL, y = "Median time (ms)", fill = NULL) +
  theme_minimal(base_size = 11) +
  theme(legend.position = "none", strip.text = element_text(face = "bold"))

Median runtime by engine (lower is better). Each panel has its own scale.

Memory

ggplot(res, aes(engine, mem_mb, fill = engine)) +
  geom_col(width = 0.7) +
  geom_text(aes(label = ifelse(mem_mb < 1, sprintf("%.2f", mem_mb),
                               sprintf("%.0f", mem_mb))), hjust = -0.15, size = 3) +
  facet_wrap(~operation, ncol = 2, scales = "free_x") +
  coord_flip() +
  scale_fill_manual(values = c(basetable = "#1b7837", data.table = "#762a83",
                               dplyr = "#c2a5cf", collapse = "#e08214")) +
  labs(x = NULL, y = "Memory allocated (MB)", fill = NULL) +
  theme_minimal(base_size = 11) +
  theme(legend.position = "none", strip.text = element_text(face = "bold"))

Memory allocated by each expression, as reported by bench (lower is better).

Reading the results

basetable beats data.table on distinct, grouped count, sd by group and semi join, and is level with it on equi join and (on the common col <op> scalar shape) filter. Against dplyr it is faster across the board, often by an order of magnitude on the grouped operations.

collapse is the fastest engine on most of these operations, including filter, distinct, grouped count and both joins; basetable is ahead of it on grouped sd. String sort is the operation where the single-thread gap is widest: orderrows() is a stable parallel radix, ~20x faster than base order(), but collapse and data.table are faster when limited to one thread.

The memory panel is where basetable separates from all three. A grouped aggregate or count in basetable reduces inside the C++ engine without building intermediate columns, so it allocates a fraction of a megabyte where data.table and dplyr allocate tens and collapse a few.

The advantage over data.table widens with size and cardinality: inst/benchmarks/benchmark-scale.R at 1e6 rows with ~100k groups puts semimerge near 0.25x of data.table and grouped count / sd well below 1.

Numbers are machine- and size-specific. Set BT_VIGNETTE_N / BT_VIGNETTE_REPS to reproduce at other sizes.