slow nanoarrow_array_stream to data.frame for strings; slow ALTREP materialization

[klin333]

• Creating a data.frame directly from a nanoarrow_array_stream is much slower than first converting to an arrow::Table and then to data.frame.
• I have tried to force ALTREP materialization in both paths for a fair comparison, and confirmed via .Internal(inspect(x)).
• The key observation is that the direct nanoarrow_array_stream → data.frame() path materializes much slower than the Arrow Table path.
• This is very similar to R: Benchmarking ADBC (Snowflake) vs ODBC shows issue with larger datasets arrow-adbc#2508 . In fact, the workaround of going via arrow Table is inspired by that issue. However, even though that issue is closed, the reprex below for strings show significant performance difference persists.

The elapsed time to fetch, convert and materialize 100k rows of random strings, of various column sizes, are shown below. The route via Arrow::Table scales about linearly, while the direct route from nanoarrow_arrow_stream to data.frame scales much worse than linearly.

num_cols	elapsed_with_arrow	elapsed_without_arrow
10	2.4 secs	1.3 secs
20	3.2 secs	2.9 secs
40	6.1 secs	7.0 secs
80	12.7 secs	30.8 secs
160	26.9 secs	920.7 secs

get_con <- function() {
  uri <- "secret"
  adbc_con <- adbcsnowflake::adbcsnowflake() |>
    adbcdrivermanager::adbc_database_init(uri = uri) |>
    adbcdrivermanager::adbc_connection_init()
  adbc_con
}

sweep_cols <- c(10, 20, 40, 80, 160)
results_df <- purrr::map_dfr(sweep_cols, function(num_cols) {
  print(sprintf("num_cols = %i", num_cols))
  
  # write table to snowflake. use separate R process to avoid common string pool
  callr::r(args = list(get_con = get_con, num_cols = num_cols), function(get_con, num_cols) {
    adbc_con <- get_con()
    num_rows <- 1e5
    df <- lapply(seq(num_cols), function(jj) uuid::UUIDgenerate(n = num_rows)) |> 
      rlang::set_names(paste0('x', seq(num_cols))) |>  
      dplyr::bind_cols()
    lobstr::obj_size(df)
    
    adbcdrivermanager::execute_adbc(adbc_con, "DROP TABLE IF EXISTS LARGE1;")
    adbcdrivermanager::write_adbc(df, adbc_con, "LARGE1", mode = "default")
    NULL
  })
  
  # read table with as_arrow_table, use separate R process to avoid common string pool
  elapsed_with_arrow <- callr::r(args = list(get_con = get_con), function(get_con) {
    adbc_con <- get_con()
    t1 <- Sys.time()
    df_read <- adbcdrivermanager::read_adbc(adbc_con, 'SELECT * from "LARGE1"') |> 
      arrow::as_arrow_table() |>
      as.data.frame()
    .Internal(inspect(df_read[[1]]))          # shows 16 STRSXP g0c0 [REF(65535)] arrow::array_string_vector<0x00000299299da350, string, 687 chunks, 0 nulls> len=100000
    tibble_read <- tibble::as_tibble(df_read) # required in order for print to trigger ALTREP materialization
    .Internal(inspect(df_read[[1]]))          # shows: 16 STRSXP g0c0 [REF(65535)] arrow::array_string_vector<0x00000299299da350, string, 687 chunks, 0 nulls> len=100000
    print(tibble_read)                        # takes a bit of time. ALTREP materialization happens here
    .Internal(inspect(df_read[[1]]))          # shows: 16 STRSXP g1c0 [MARK,REF(65535)] materialized arrow::array_string_vector len=100000
    Sys.time() - t1
  })
  
  # read table without as_arrow_table, use separate R process to avoid common string pool. SLOW
  elapsed_without_arrow <- callr::r(args = list(get_con = get_con), function(get_con) {
    adbc_con <- get_con()
    t1 <- Sys.time()
    df_read <- adbcdrivermanager::read_adbc(adbc_con, 'SELECT * from "LARGE1"') |> 
      as.data.frame()                         # slow: materialization happened here
    .Internal(inspect(df_read[[1]]))          # shows STRSXP g1c7 [MARK,REF(1)] (len=100000, tl=0) ie fully materialized standard R vector
    tibble_read <- tibble::as_tibble(df_read) # instant
    print(tibble_read)                        # instant
    Sys.time() - t1
  })
  
  data.frame(num_cols, elapsed_with_arrow, elapsed_without_arrow)
})

results_df
# num_cols elapsed_with_arrow elapsed_without_arrow
# 1       10      2.454586 secs         1.327180 secs
# 2       20      3.211402 secs         2.942045 secs
# 3       40      6.179819 secs         7.045094 secs
# 4       80     12.713749 secs        30.818208 secs

> library(arrow)
> library(nanoarrow)
> library(adbcdrivermanager)
> library(adbcsnowflake)
> sessionInfo()
> sessionInfo()
R version 4.5.2 (2025-10-31 ucrt)
Platform: x86_64-w64-mingw32/x64
Running under: Windows 11 x64 (build 22631)

Matrix products: default
  LAPACK version 3.12.1

locale:
[1] LC_COLLATE=English_Australia.utf8  LC_CTYPE=English_Australia.utf8
[3] LC_MONETARY=English_Australia.utf8 LC_NUMERIC=C
[5] LC_TIME=English_Australia.utf8

time zone: Australia/Sydney
tzcode source: internal

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base

other attached packages:
[1] arrow_22.0.0             adbcsnowflake_0.20.0     adbcdrivermanager_0.20.0
[4] nanoarrow_0.7.0-1

loaded via a namespace (and not attached):
 [1] assertthat_0.2.1 R6_2.6.1         bit_4.6.0        tidyselect_1.2.1
 [5] magrittr_2.0.4   glue_1.8.0       tibble_3.3.0     pkgconfig_2.0.3
 [9] bit64_4.6.0-1    dplyr_1.1.4      generics_0.1.4   lifecycle_1.0.4
[13] ps_1.9.1         cli_3.6.5        processx_3.8.6   callr_3.7.6
[17] vctrs_0.6.5      compiler_4.5.2   purrr_1.1.0      pillar_1.11.1
[21] rlang_1.1.6

Suggestions from ChatGPT (I have absolutely no idea if it's sensible):

• Replicate Arrow's faster materialization algorithm inside nanoarrow (C/C++), focusing on:
• Chunked processing of record batches (avoid per‑row overhead across the whole table).
• Minimized PROTECT/UNPROTECT frequency (allocate target STRSXP once per column; protect vectors, not each element).
• Avoid repeated re‑encoding: call Rf_mkCharLenCE(..., CE_UTF8) using direct string buffer/offsets; bypass translateCharUTF8 in tight loops.
• Efficient null handling (bitmap/offset reads with branch‑light loops).
• Optional parallel chunk materialization for very wide tables (behind an option; respect R’s thread‑safety constraints).

[klin333]

this following path, which also tries to go via arrow::Table, is just as slow as the direct nanoarrow->data.frame path. not sure if this give any ideas to root cause of problem?

library(tidyverse)
uri <- "secret"
con <- DBI::dbConnect(adbi::adbi("adbcsnowflake::adbcsnowflake"), uri = uri)

t1 <- Sys.time()
x_tbl <- tbl(con, 'LARGE1') 
stream <- DBI::dbGetQueryArrow(con, as.character(dbplyr::sql_render(x_tbl, con)))
df <- arrow::as_arrow_table(stream) %>% as.data.frame()
.Internal(inspect(df[[1]])) # 16 STRSXP g0c0 [REF(65535)] arrow::array_string_vector<0x000002468862c460, string
df <- as_tibble(df)
print(df) # just as slow as direct path
.Internal(inspect(df[[1]])) # 16 STRSXP g0c0 [REF(65535)] materialized arrow::array_string_vector len=100000
print(Sys.time() - t1)

[paleolimbot]

Thank you for investigating! The conversion definitely needs some work...I started a PR some time ago to improve it but didn't have a chance to finish. Perhaps now with LLMs we can do much better!

The non-linearness is concerning...I wonder if that is related to the cost of growing the string pool or whether there's some conversion that is happening more that it should.

this following path, which also tries to go via arrow::Table, is just as slow as the direct nanoarrow->data.frame path. not sure if this give any ideas to root cause of problem?

I have had problems in the past benchmarking this kind of thing because putting that many R objects into the session makes it very hard for the garbage collector to keep up (particularly when benchmarking, since this puts intense pressure by creating and deleting objects rapidly).

In general I would expect the materialization of large amounts of strings to perform the same with both nanoarrow and arrow because there's really just no avoiding all of the calls to the R API + the global string pool.

[paleolimbot]

Ok, I took some time to try to reproduce this...it might take some larger numbers to properly observe the non-linearity. Feel free to modify this example to better reproduce! I am seeing a few erratic results and I think that putting millions of strings into the session starts to make things difficult to predict, possibly because garbage collection starts to take a long time.

library(nanoarrow)

ascii_bytes <- vapply(letters, charToRaw, raw(1), USE.NAMES = FALSE)

random_string_array <- function(n = 1, n_chars = 16) {
  data_buffer <- sample(ascii_bytes, n_chars * n, replace = TRUE)
  offsets_buffer <- as.integer(seq(0, n * n_chars, length.out = n + 1))
  nanoarrow_array_modify(
    nanoarrow_array_init(na_string()),
    list(
      length = n,
      null_count = 0,
      buffers = list(NULL, offsets_buffer, data_buffer)
    )
  )
}

random_string_struct <- function(n_rows = 1024, n_cols = 1, n_chars = 16) {
  col_names <- sprintf("col%03d", seq_len(n_cols))
  col_types <- rep(list(na_string()), n_cols)
  names(col_types) <- col_names
  schema <- na_struct(col_types)
  
  columns <- lapply(
    col_names,
    function(...) random_string_array(n_rows, n_chars = n_chars)
  )
  
  nanoarrow_array_modify(
    nanoarrow_array_init(schema),
    list(
      length = n_rows,
      null_count = 0,
      children = columns
    )
  )  
}

random_string_stream <- function(n_batches = 1, n_rows = 1024, n_cols = 1, n_chars = 16) {
  basic_array_stream(
    lapply(
      seq_len(n_batches),
      function(...) random_string_struct(n_rows, n_cols, n_chars)
    )
  )
}
time_convert <- function(n_batches, n_cols, n_rows, method) {
  message(glue::glue("generating n_batches = {n_batches}, n_cols = {n_cols}, method = {method}"))
  stream <- random_string_stream(n_batches = n_batches, n_cols = n_cols, n_rows = n_rows)

  message("timing...")
  switch(method,
    convert_array_stream_explicit_size = {
      system.time({
        df <- convert_array_stream(stream, size = n_batches * n_rows)
        stopifnot(
          nrow(df) == (n_batches * n_rows),
          ncol(df) == n_cols
        )
      })
    },
    convert_array_stream_default = {
      system.time({
        df <- convert_array_stream(stream)
        stopifnot(
          nrow(df) == (n_batches * n_rows),
          ncol(df) == n_cols
        )
      })
    },
    convert_arrow_table_no_altrep = {
      withr::with_options(list(arrow.use_altrep = FALSE), {
        system.time({
          df <- as.data.frame(arrow::as_arrow_table(stream))
          stopifnot(
            nrow(df) == (n_batches * n_rows),
            ncol(df) == n_cols
          )
        })
      })
    },
    stop("Unknown method")
  )
}

times <- expand.grid(n_batches = c(10, 20, 30, 40, 50, 60), n_cols = c(10, 20, 30, 40, 50, 60), method = c(
  "convert_array_stream_explicit_size",
  "convert_array_stream_default",
  "convert_arrow_table_no_altrep"
), stringsAsFactors = FALSE)

times$system_time <- Map(time_convert, times$n_batches, times$n_cols, 1024, times$method)
times$elapsed <- vapply(times$system_time, "[[", double(1), 1)

library(ggplot2)

# These are somewhere in the middle and not at the largest number of values,
# so I think they really are just the unlucky runs where the gc ran
weird_gc <- times$method == "convert_array_stream_explicit_size" & 
  times$n_cols == 40 & 
  times$n_batches %in% c(50, 60)

# facet by n_cols to see the effect of increasing batch size
ggplot(times[!weird_gc, ], aes(n_batches, elapsed, fill = method)) +
  geom_col(position = position_dodge()) +
  facet_wrap(~n_cols) +
  theme_bw() +
  theme(legend.position = "bottom")

# facet by n_batches to see the effect of increasing columns
ggplot(times[!weird_gc, ], aes(n_cols, elapsed, fill = method)) +
  geom_col(position = position_dodge()) +
  facet_wrap(~n_batches) +
  theme_bw() +
  theme(legend.position = "bottom")

[klin333]

Thanks. I’ll have a look at Monday, but at first glance, it seems you are creating the random strings and converting array stream in the same R session. This will not reproduce the error because creating the string already put them in the R global string pool for that r session, so during convert array stream, the r global string pool doesn’t need to change at all!

That’s why in my reprex, I used callr to launch independent r processes, to ensure that the random strings are created in a different r session, so that convert array stream need to build up the R global string pool from scratch. This is exactly what happens when reading data from remote database. perhaps write to a on disk parquet file? I can give that a go on Monday

[klin333]

Hm, i can no longer reproduce the error (my original reprex in OP that showed 920.7 seconds for ncols=160), if i change from snowflake adbc to adbcsqlite, ie this:

adbc_con <- adbcsqlite::adbcsqlite() |>
    adbcdrivermanager::adbc_database_init(uri = 'tests/test.adbcsqlite') |>
    adbcdrivermanager::adbc_connection_init()

yeah i'm super confused now... I don't think it's an issue isolated to me, as others have reported slow snowflake adbc connections apache/arrow-adbc#2508 . i don't know much about adbcsqlite to know if this observation is sufficient to isolate the problem to snowflake adbc.

[klin333]

Ok, I had an epiphany. I am using snowflake adbc multi-batch array stream of lots of unique strings. Normally convert_array_stream() will result in exponential time hanging (the call itself finishes fine, but the immediate next arbitrary line will have exponential hang).

But, by supplying convert_array_stream() with known size, the exponential time problem is gone. See illustrative example below. The exact same multi-batch snowflake adbc stream. Only difference is supplying the known size.

This means, the inefficiency is in the collect_array_stream() and nanoarrow_c_basic_array_stream() parts, ie restreaming in R. ChatGPT reasons that it is likely due to the recursive array_export() in nanoarrow_c_basic_array_stream(), resulting in large number of crossing the C and R boundary, that eventually cripples R garbage collection, and/or global string pool.

The logical solution seems to be: calculate the array_stream total length, and restream in C. This massively reduces the C/R boundary crossing. I've done it here in a fork: main...klin333:arrow-nanoarrow:feature-r-restream The C function nanoarrow_c_array_stream_total_length is totally written by ChatGPT, so if this is the right way to go, heavy code review is required.

On the bright side, this proposed fork totally fixes the exponential time behaviour observed in my original post. And it does so without my previous workaround of using convert_array. This directly fixes the problem with convert_array_stream.

uri <- "secret"
adbc_con <- adbcsnowflake::adbcsnowflake() |>
  adbcdrivermanager::adbc_database_init(uri = uri) |>
  adbcdrivermanager::adbc_connection_init()

t1 <- Sys.time()
stream <- adbcdrivermanager::read_adbc(adbc_con, 'SELECT * from "LARGE160"')

x <- stream
to <- nanoarrow::infer_nanoarrow_ptype(x$get_schema())
df <- nanoarrow::convert_array_stream(x, to)  # setting size = 1e5 (known value) here fixes exponential time problem
.Internal(inspect(df[[1]]))  # note weirdly, without size argument, it's this line (any command) after convert_array_stream that hangs
df <- tibble::as_tibble(df)
.Internal(inspect(df[[1]]))
gc()
print(df)
print(Sys.time() - t1)

Same test from original post, after proposed restream in C:

num_cols	elapsed_with_arrow	elapsed_without_arrow
10	1.7 secs	1.6 secs
20	4.4 secs	2.9 secs
40	7.1 secs	6.4 secs
80	12.9 secs	15.7 secs
160	28.4 secs	27.2 secs

[klin333]

Yep, further diagnosis confirmed it's gc() after restream in R, that hangs for exponentially long time as the amount of unique strings go up.

In the snippet below, i can trigger the 900+ seconds of hang (with 100k rows and 160 columns of unique strings), by simply calling gc() - no nanoarrow_c_convert_array_stream is done at all, it's just restreaming in R.

Seems to be consistent with my prior observations that convert_array_stream itself seems to return in linear time, but then any subsequent random R command hang in exponential time - that's probably when gc() got triggered, where R gets crippled trying to gc the batches and basic_stream from the local stack of convert_array_stream()

batches <- collect_array_stream(
  array_stream,
  n,
  schema = schema,
  validate = FALSE
)
basic_stream <- .Call(nanoarrow_c_basic_array_stream, batches, schema, FALSE)

# any of these will hang in exponential time as the amount of unique strings increase

# 1)
rm(batches)
gc() # hangs

# 2)
rm(basic_stream)
gc() # hangs

# 3)
rm(batches, basic_stream)
gc() # hangs

[klin333]

ok, so now i think we have two viable solutions to my initial github issue of slow as.data.frame(nanoarrow_array_stream), ie slow convert_array_stream:

1. collect array stream to batches of arrays in R, then nanoarrow_c_convert_array, then bind rows in R
2. calculate array stream total length and restream both in C in order to avoid crossing the C/R boundary, then nanoarrow_c_convert_array_stream with calculated total length

Both methods avoid the .Call(nanoarrow_c_basic_array_stream, batches) which blows up gc(). I suppose it may make sense to see if nanoarrow_c_basic_array_stream itself can be fixed, but that's way beyond my capabilities.

[paleolimbot]

Thank you for the investigation! You are absolutely correct...something is wrong with the converter (perhaps it is creating many, many reference cycles that the garbage collector is having a hard time solving). It could also be a result of R_PreserveObject (switching to cpp11 may help). It may take me some time to circle back and fix this properly but I will do it!