Comparison of DBI/odbc, DBI/RODBCDBI, and RODBC db access for R

DBI RODBC comparison.R

## ------------------------------------------------------------------
## SETUP

# docker run -d -p "31433:1433" --name testss \
#   -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=Mysecretpassword1" \
#   -e "MSSQL_PID=Developer" --cap-add SYS_PTRACE \
#   microsoft/mssql-server-linux

# ... and add 'testss' as a DSN in the system- or user- odbc configuration

## ------------------------------------------------------------------
## CONNECTIONS

odbcdbi <- DBI::dbConnect(odbc::odbc(), dsn="testss", uid="SA", pwd="Mysecretpassword1")
rodbcdbi <- DBI::dbConnect(RODBCDBI::ODBC(), dsn="testss", uid="SA", pwd="Mysecretpassword1", case = "nochange")
rodbc <- RODBC::odbcConnect(dsn="testss", uid="SA", pwd="Mysecretpassword1")

## ------------------------------------------------------------------
## SAMPLE DATA

data("flights", package = "nycflights13")
head(flights, 10)
# # A tibble: 10 x 19
#     year month   day dep_time sched_dep_time dep_delay arr_time sched_arr_time
#    <int> <int> <int>    <int>          <int>     <dbl>    <int>          <int>
#  1  2013     1     1      517            515         2      830            819
#  2  2013     1     1      533            529         4      850            830
#  3  2013     1     1      542            540         2      923            850
#  4  2013     1     1      544            545        -1     1004           1022
#  5  2013     1     1      554            600        -6      812            837
#  6  2013     1     1      554            558        -4      740            728
#  7  2013     1     1      555            600        -5      913            854
#  8  2013     1     1      557            600        -3      709            723
#  9  2013     1     1      557            600        -3      838            846
# 10  2013     1     1      558            600        -2      753            745
# # ... with 11 more variables: arr_delay <dbl>, carrier <chr>, flight <int>,
# #   tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
# #   hour <dbl>, minute <dbl>, time_hour <dttm>

## ------------------------------------------------------------------
## WRITE TEST

dat <- head(flights, 10)[,-19]
microbenchmark::microbenchmark(
  odbcdbi  = DBI::dbWriteTable(odbcdbi, "flights", dat),
  rodbcdbi = DBI::dbWriteTable(rodbcdbi, "flights", dat),
  rodbc    = RODBC::sqlSave(rodbc, dat, "flights"),
  setup = DBI::dbExecute(odbcdbi, "drop table flights"),
  times = 10
)
# Unit: milliseconds
#      expr      min       lq      mean   median       uq      max neval
#   odbcdbi  42.5866  48.1068  63.02283  52.3726  54.9726 175.0545    10
#  rodbcdbi 120.9206 124.8994 147.82120 128.5527 133.4413 261.1105    10
#     rodbc 117.3514 124.5346 128.05489 126.8475 131.7800 138.7100    10

dat <- head(flights, 10000)[,-19]
microbenchmark::microbenchmark(
  odbcdbi  = DBI::dbWriteTable(odbcdbi, "flights", dat),
  rodbcdbi = DBI::dbWriteTable(rodbcdbi, "flights", dat),
  rodbc    = RODBC::sqlSave(rodbc, dat, "flights"),
  setup = DBI::dbExecute(odbcdbi, "drop table flights"),
  times = 10
)
# Unit: milliseconds
#      expr        min         lq       mean     median         uq        max  neval
#   odbcdbi   311.3064   346.2952   370.1984   360.6649   392.0773   463.7257     10
#  rodbcdbi 76299.2259 77817.4400 82619.1613 81581.8538 87358.8572 89985.6128     10
#     rodbc 74530.2949 76333.3798 79932.4808 81056.4030 82712.8733 85201.5292     10


## ------------------------------------------------------------------
## WHOLE-TABLE READ

microbenchmark::microbenchmark(
  odbcdbi  = DBI::dbReadTable(odbcdbi, "flights"),
  rodbcdbi = DBI::dbReadTable(rodbcdbi, "flights"),
  rodbc    = RODBC::sqlFetch(rodbc, "flights"),
  times = 10
)
# Unit: seconds
#      expr       min        lq      mean    median        uq       max neval
#   odbcdbi  1.888136  2.242207  2.378295  2.380542  2.537712  3.037383    10
#  rodbcdbi 18.302842 20.572316 22.148530 22.180591 23.939045 25.751739    10
#     rodbc 19.968433 21.002547 22.787167 21.709641 24.553324 28.107682    10


## ------------------------------------------------------------------
## QUERY (whole-table) READ

microbenchmark::microbenchmark(
  odbcdbi  = DBI::dbGetQuery(odbcdbi, "select * from flights"),
  rodbcdbi = DBI::dbGetQuery(rodbcdbi, "select * from flights"),
  rodbc    = RODBC::sqlQuery(rodbc, "select * from flights"),
  times = 10
)
# Unit: seconds
#      expr       min        lq      mean    median        uq       max neval
#   odbcdbi  1.445874  1.836158  1.884076  1.931463  1.944022  2.057212    10
#  rodbcdbi 17.304687 18.970654 20.042980 19.982043 21.247385 22.101437    10
#     rodbc 19.015758 19.243007 20.319214 19.957215 21.721255 22.245261    10

## ------------------------------------------------------------------
## QUERY (partial) READ

microbenchmark::microbenchmark(
  odbcdbi  = DBI::dbGetQuery(odbcdbi, "select top 1000 * from flights"),
  rodbcdbi = DBI::dbGetQuery(rodbcdbi, "select top 1000 * from flights"),
  rodbc    = RODBC::sqlQuery(rodbc, "select top 1000 * from flights"),
  times = 10
)
# Unit: milliseconds
#      expr     min      lq     mean   median      uq     max neval
#   odbcdbi 10.5405 11.2504 11.75502 11.78765 12.0187 13.1082    10
#  rodbcdbi 36.1839 36.7193 37.96366 37.46475 38.9476 41.6548    10
#     rodbc 37.4643 37.7944 38.42617 37.94985 38.5589 41.2796    10