Optimising Oracle DB Connections from R

Benchmarking write and read speed across drivers and data encodings

Overview

This report documents a systematic investigation into the performance of DST Oracle database connections from R, testing several orthogonal strategies for speeding up both writes and reads against the STATPROD database.

The strategies evaluated are:

1. Driver choice — ROracle vs ODBC
2. Bulk-fetch size — default (1 k rows) vs enlarged (50 k rows) fetch buffer
3. Optimized write function — write_oracle_optimized() vs plain dbWriteTable()
4. Column type encoding — raw 4 k VARCHAR2 vs right-sized VARCHAR2 vs integer-encoded columns
5. Date encoding — native DATE vs yyyymmdd character strings vs yyyymmdd integers
6. Combined optimization — the full pipeline applied to the complete Lifelines table (~10 M rows)

---

Setup

Packages and environment

library(tidyverse)
library(arrow)
library(dbplyr)
library(DBI)
library(ROracle)
library(odbc)
library(microbenchmark)
library(tictoc)
library(here)

Sys.setenv(TZ       = "Europe/Copenhagen")
Sys.setenv(ORA_SDTZ = "Europe/Copenhagen")

Database connections

Two connections to STATPROD are established: one via ROracle (the native Oracle driver) and one via ODBC. A third ROracle connection with an enlarged bulk fetch buffer (bulk_size = 5e4) is used later to test read performance.

# Native ROracle connection (default bulk_size = 1 000)
conn <- dbConnect(
  ROracle::Oracle(),
  username = "[D221911]",
  dbname   = "STATPROD"
)

# ODBC connection
# Discover available drivers with:
# odbc::odbcListDrivers() |> dplyr::filter(grepl("Oracle", name, ignore.case = TRUE))
conn_odbc <- DBI::dbConnect(
  odbc::odbc(),
  driver = "Oracle in instantclient_21_12",
  uid    = "[D221911]",
  dbq    = "STATPROD"
)

# ROracle connection with enlarged fetch buffer
conn_50k <- dbConnect(
  ROracle::Oracle(),
  username  = "[D221911]",
  dbname    = "STATPROD",
  bulk_size = 5e4
)

Test data

The full Lifelines parquet file is read from disk; a 1 000-row subset is also used for the driver and encoding comparisons to check robustness of speed gains.

ll25 <- read_parquet("K:/Lifelines/prq/ll25.parquet")

# 1 000-row subset for fast iteration
ll1k <- ll25 |> slice(1:1000)

---

1 — Driver comparison: ROracle vs ODBC

Write speed

ROracle and ODBC are benchmarked writing the same 1 000-row table 100 times.

tic()
microbenchmark(
  ROracle = dbWriteTable(conn,      "LL1K", ll1k, overwrite = TRUE),
  ODBC    = dbWriteTable(conn_odbc, "LL1K", ll1k, overwrite = TRUE),
  times   = 30
)

Unit: milliseconds
    expr     min      lq     mean  median      uq     max neval cld
 ROracle 66.7688 68.0693 70.51418 69.8985 70.5805 86.0294    30  a 
    ODBC 24.4367 25.3154 30.87561 28.2432 31.1112 73.4027    30   b

toc()

4.77 sec elapsed

Read speed

tic()
microbenchmark(
  ROracle = conn      |> tbl(in_schema("D221911", "LL1K")) |> collect(),
  ODBC    = conn_odbc |> tbl(in_schema("D221911", "LL1K")) |> collect(),
  times   = 30
)

Unit: milliseconds
    expr     min      lq     mean   median      uq     max neval cld
 ROracle 19.2190 20.3295 21.80979 21.71875 22.4337 27.9216    30  a 
    ODBC 37.4751 38.8602 41.45886 40.13715 41.8763 56.1186    30   b

toc()

2.61 sec elapsed

CONCLUSION: use ROracle for reading the data from the database to R; and use ODBC for writing an R tibble to Oracle database.

---

2 — Bulk fetch size effect on read speed (ROracle)

ROracle fetches rows from Oracle in batches. The default batch size is 1 000 rows. Enlarging it to 50 000 rows reduces round-trips and can substantially cut read times for large tables.

tic()
microbenchmark(
  ro1k = {
    df <- conn     |> tbl(in_schema("D221911", "LIFELINES")) |> collect()
    rm(df); gc()
  },
  ro50k = {
    df <- conn_50k |> tbl(in_schema("D221911", "LIFELINES")) |> collect()
    rm(df); gc()
  },
  times = 3
)

Unit: seconds
  expr     min       lq     mean   median       uq      max neval cld
  ro1k 38.2160 38.29635 38.40906 38.37669 38.50560 38.63450     3   a
 ro50k 38.3062 38.35610 38.91209 38.40600 39.21504 40.02407     3   a

toc()

232.7 sec elapsed

CONCLUSION: Against the expectations, this step is not bringing any speedup.

---

3 — Optimised write function

Function write_oracle_optimized() scans the data frame and infers the tightest Oracle data type for every column.

The main issue it addresses is avoiding the ROracle driver’s default VARCHAR2(4000 CHAR) assignment to every character column. By default when writing an R tibble into Oracle database ROracle assigns 4k bytes per character column, apparently to stay on the safe side. As a result, all the character columns use the maximum declared width even when the actual strings are short. This is a critical optimisation for read speed, as it avoids unnecessary I/O of large amounts of padding. The issue is specifically critical for further fetching of the database table in SAS, which blindly assigns 4k to every character column regardless of the actual size.

source("oprimize-r-oracle/write-oracle-optimized.R" |> here())

tic()
microbenchmark(
  naive     = dbWriteTable(conn, "LL1K", ll1k, overwrite = TRUE),
  optimised = write_oracle_optimized(conn, "LL1K", ll1k, overwrite = TRUE),
  times     = 10
)

Unit: milliseconds
      expr     min      lq     mean   median      uq      max neval cld
     naive 66.8124 68.0199 69.58495 69.14940 71.2615  73.7167    10   a
 optimised 68.6909 69.3863 74.04736 69.91055 73.1206 106.4447    10   a

toc()

2.24 sec elapsed

CONCLUSION: Writing R data frames to Oracle with write_oracle_optimized() is only marginally slower than dbWriteTable() due to the overhead of scanning the data frame and inferring optimal column types, but the read speed benefits are substantial and outweigh the initial write cost.

---

4 — Effect of column data types on read speed

Three variants of the 1 000-row test table are compared:

Table	Description
LL1K	Default dbWriteTable() — all strings as 4 k
LL1K_OPT	write_oracle_autosize() — right-sized VARCHAR2
LL1K_INT	Character columns replaced by integer factor codes

source("oprimize-r-oracle/read-oracle-optimized.R" |> here())

# Default 4 k char table
dbWriteTable(conn, "LL1K", ll1k |> rename_with(toupper), overwrite = TRUE)

[1] TRUE

# Right-sized VARCHAR2
write_oracle_optimized(
  conn_odbc, "LL1K_OPT",
  ll1k |> rename_with(toupper),
  overwrite = TRUE
)

# Integer-encoded character columns
ll1k_int <- ll1k |>
  mutate(
    person_id         = as.factor(person_id)         |> as.integer(),
    kilde_start       = as.factor(kilde_start)       |> as.integer(),
    event_cause_start = as.factor(event_cause_start) |> as.integer(),
    kilde_final       = as.factor(kilde_final)       |> as.integer(),
    event_cause_final = as.factor(event_cause_final) |> as.integer()
  )

write_oracle_optimized(
  conn_odbc, "LL1K_INT",
  ll1k_int |> rename_with(toupper),
  overwrite = TRUE
)

tic()
microbenchmark(
  `4k`      = conn |> tbl(in_schema("D221911", "LL1K"))    |> collect(),
  optimised = conn |> tbl(in_schema("D221911", "LL1K_OPT"))   |> collect(),
  integer   = conn |> tbl(in_schema("D221911", "LL1K_INT"))|> collect(),
  times     = 30
)

Unit: milliseconds
      expr     min      lq     mean   median      uq     max neval cld
        4k 20.3128 21.9776 23.18556 22.49440 24.1095 30.0746    30  a 
 optimised 13.5939 14.9162 15.51871 15.37265 16.2250 17.5666    30   b
   integer 13.0214 13.9065 15.08535 14.45920 15.4422 23.2819    30   b

toc()

2.32 sec elapsed

CONCLUSION: Right-sizing character columns with write_oracle_optimized() yields a significant read speedup, and encoding character columns as integers yields an even larger speedup.

---

5 — Date encoding strategies

Dates stored as Oracle DATE types are expensive to deserialise in R.

Two alternative encodings are tested:

• yyyymmdd character strings — converted with a tiny self-written helper function to_yyyymmdd_anytime()
• yyyymmdd integers — converted with to_yyyymmdd_anytime_int()

source("oprimize-r-oracle/to_yyymmdd_int.R" |> here())

This conversion conversion is quite costly (≈ 200 s for the full 10 M row table) but we only need to do it once, and once the optimized table is stored in the database, the future reads of the table will be significantly faster.

Char-format dates (yyyymmdd as string)

tic()
ll_char <- ll25 |> to_yyyymmdd_anytime()
toc()

259.62 sec elapsed

tic()
write_oracle_optimized(
  conn_odbc, "LL_CHAR_DATE",
  ll_char |> rename_with(toupper),
  overwrite = TRUE
)
toc()

40.99 sec elapsed

Integer dates (yyyymmdd as NUMBER)

tic()
ll_int <- ll25 |> to_yyyymmdd_anytime_int()
toc()

269.79 sec elapsed

tic()
write_oracle_optimized(
  conn_odbc, "LL_INT_DATE",
  ll_int |> rename_with(toupper),
  overwrite = TRUE
)
toc()

34.23 sec elapsed

Integer dates + encoded character columns

On top of integer dates, all remaining character columns are replaced with integer codes stored in a codebook. This is the most aggressive encoding strategy, but it yields the best read times and protects against accidental encoding breaking changes.

source("oprimize-r-oracle/encode_chars_to_int.R" |> here())

tic()
ll_coded <- ll_int |> encode_chars_to_int()
toc()

6.61 sec elapsed

tic()
write_oracle_optimized(
  conn_odbc, "LL_INT_DATE_CODED",
  ll_coded$encoded |> rename_with(toupper),
  overwrite = TRUE
)
toc()

21.33 sec elapsed

---

6 — Ultimate benchmark: full Lifelines table

All four versions of the full table are read back via conn (default bulk_size = 1 000) and timed over 3 repetitions.

microbenchmark(
  original        = conn |> tbl(in_schema("D221911", "LIFELINES"))         |> collect(),
  char_dates      = conn |> tbl(in_schema("D221911", "LL_CHAR_DATE"))      |> collect(),
  int_dates       = conn |> tbl(in_schema("D221911", "LL_INT_DATE"))       |> collect(),
  int_dates_coded = conn |> tbl(in_schema("D221911", "LL_INT_DATE_CODED")) |> collect(),
  times = 3
)

Unit: seconds
            expr       min        lq      mean    median        uq      max
        original 39.136311 39.282511 39.381276 39.428711 39.503759 39.57881
      char_dates 15.605139 15.674255 15.734186 15.743372 15.798710 15.85405
       int_dates 12.230229 12.556681 12.708352 12.883133 12.947413 13.01169
 int_dates_coded  9.361526  9.444238  9.644585  9.526951  9.786115 10.04528
 neval  cld
     3 a   
     3  b  
     3   c 
     3    d

Results are clear. Encoding all columns as integers yields a ~4× read speed-up with no change to the Oracle driver or connection settings.

---

Summary

Here are the main results, briefly:

1. Driver choice — ROracle for reading and ODBC for writing.
2. bulk_size — enlarging the ROracle fetch buffer didn’t yield anticipated speedup.
3. Right-size column types (write_oracle_optimized) — avoids 4 k VARCHAR2 padding; the foundation for all subsequent gains.
4. Encode dates as integers — converts DATE → NUMBER(10,0) in yyyymmdd format; straightforward and safe to reverse.
5. Encode character columns as integers — replaces low-cardinality string columns with integer codes backed by a codebook. This format yields the best read times.