Bug: derive_vars_joined is slow or crashing on big datasets

[otts5]

What happened?

I've been trying to run derive_vars_joined to add the visit information. The function was already slow in the beginning, but now with more records it actually breaks the system (Roche System Apollo). I had to write my own code to do the task.
Is there a way to improve the function so it can cope with large data (>7Mio records)?
Thanks!

Session Information

R version 4.2.2 Patched (2022-11-10 r83330)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Ubuntu 20.04.6 LTS

Matrix products: default
BLAS: /usr/lib/x86_64-linux-gnu/openblas-pthread/libblas.so.3
LAPACK: /usr/lib/x86_64-linux-gnu/openblas-pthread/liblapack.so.3

locale:
[1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C
[3] LC_TIME=en_US.UTF-8 LC_COLLATE=en_US.UTF-8
[5] LC_MONETARY=en_US.UTF-8 LC_MESSAGES=en_US.UTF-8
[7] LC_PAPER=en_US.UTF-8 LC_NAME=C
[9] LC_ADDRESS=C LC_TELEPHONE=C
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C

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

other attached packages:
[1] shiny_1.7.4 CDSE_2.1.1.3 aws.signature_0.6.0
[4] aws.s3_0.3.21 data.table_1.14.8 forcats_1.0.0
[7] purrr_1.0.2 readr_2.1.4 tidyr_1.3.0
[10] tibble_3.2.1 ggplot2_3.4.1 tidyverse_1.3.2
[13] arrow_13.0.0 stringr_1.5.0 lubridate_1.9.2
[16] dplyr_1.1.2 admiralroche_0.2.0 admiral_0.11.1
[19] metatools_0.1.5 metacore_0.1.2

loaded via a namespace (and not attached):
[1] httr_1.4.4 bit64_4.0.5 jsonlite_1.8.4
[4] modelr_0.1.10 assertthat_0.2.1 googlesheets4_1.0.1
[7] cellranger_1.1.0 yaml_2.3.7 pillar_1.9.0
[10] backports_1.4.1 glue_1.6.2 digest_0.6.31
[13] promises_1.2.0.1 rvest_1.0.3 colorspace_2.1-0
[16] htmltools_0.5.4 httpuv_1.6.9 pkgconfig_2.0.3
[19] broom_1.0.3 haven_2.5.3 xtable_1.8-4
[22] scales_1.2.1 later_1.3.0 compare_0.2-6
[25] tzdb_0.3.0 timechange_0.2.0 googledrive_2.0.0
[28] generics_0.1.3 ellipsis_0.3.2 DT_0.27
[31] shinyjs_2.1.0 cachem_1.0.6 withr_2.5.0
[34] cli_3.6.1 magrittr_2.0.3 crayon_1.5.2
[37] readxl_1.4.3 mime_0.12 memoise_2.0.1
[40] fs_1.6.1 fansi_1.0.4 xml2_1.3.5
[43] tools_4.2.2 hms_1.1.3 gargle_1.3.0
[46] lifecycle_1.0.3 munsell_0.5.0 reprex_2.0.2
[49] compiler_4.2.2 rlang_1.1.1 grid_4.2.2
[52] rstudioapi_0.14 htmlwidgets_1.6.1 base64enc_0.1-3
[55] gtable_0.3.1 DBI_1.1.3 curl_5.0.0
[58] R6_2.5.1 admiraldev_0.4.0 fastmap_1.1.0
[61] bit_4.0.5 utf8_1.2.3 stringi_1.7.12
[64] Rcpp_1.0.10 vctrs_0.6.3 dbplyr_2.3.0
[67] tidyselect_1.2.0

Reproducible Example

adledd_avisit <- derive_vars_joined(
  adledd_comb,
  dataset_add = visit_windows,
  by_vars = exprs(STUDYID),
  filter_join = AWLO <= ADY & ADY <= AWHI
) 

[bms63]

whoa...7 million rows. that is a lot of data.

can we leverage some other package to help us here with bigger datasets? data.table or does dbplyr have some features we can take advantage of?

maybe we will need to think of a dbadmiral?

@pharmaverse/admiral something for us to think about

[bundfussr]

whoa...7 million rows. that is a lot of data.

can we leverage some other package to help us here with bigger datasets? data.table or does dbplyr have some features we can take advantage of?

maybe we will need to think of a dbadmiral?

@pharmaverse/admiral something for us to think about

So far we did not consider the performance of admiral. But it is definitely an important topic.

I would rather solve the issue in admiral than creating a dbadmiral package. I think this would be more user friendly and easier to maintain. Using dtplyr might be an option.

However, it requires some investigation and time. So we will not solve this issue in admiral 1.0.

[bms63]

@otts5 thanks for brining this to our attention. I am going to convert to a discussion as this is a larger topic then just one function! We will start investigating after 1.0 release.

[otts5]

Thanks for looking into it! And I agree that this is not limited to this one admiral function. Considering performance and large data should definetely be done throughout the whole package. I know it's challenging and requires some new and creative ways of programming.

[bms63]

I have linked this in the RoadMap 2.0 discussion and will close.

[bms63]

#2191 (comment)

[manciniedoardo]

Hi @otts5 - can I ask, did your code work fine using one of the join functions from {dplyr}? If not, what was your workaround?

[otts5]

Hi @manciniedoardo, here is the code I've used to replace the derive_vars_joined to add the AVISIT based on a visit window dataset:

by <- join_by(STUDYID, between(ADY, AWLO, AWHI))
adledd_avisit <- 
  left_join(adledd_comb, visit_windows, by)

[manciniedoardo]

thanks! the team will investigate.