inst/doc/benchmarks.R

In odbc: Connect to ODBC Compatible Databases (using the DBI Interface)

## ----eval = FALSE, include = FALSE--------------------------------------------
#  # to run the timings in this readme, set the following env variable:
#  Sys.setenv(ODBC_EVAL_BENCHMARKS = "true")

## ----include = FALSE----------------------------------------------------------
eval_timings <- as.logical(Sys.getenv("ODBC_EVAL_BENCHMARKS", "false"))

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  eval = eval_timings
)

## ----include = FALSE----------------------------------------------------------
#  # The CRAN version of RODBC uses iODBC, so to use unixODBC we need to
#  # recompile it from source, specifying the odbc manager explicitly:
#  #
#  # install.packages("RODBC", type = "source", INSTALL_opts="--configure-args='--with-odbc-manager=odbc'")
#  #
#  # see `vignette("develop")` for more details.

## ----setup--------------------------------------------------------------------
#  library(odbc)
#  library(RODBC)
#  library(RODBCDBI)
#  
#  library(DBI)
#  
#  library(nycflights13)

## -----------------------------------------------------------------------------
#  flights$time_hour <- NULL
#  flights <- as.data.frame(flights)

## ----echo = FALSE-------------------------------------------------------------
#  options("connectionObserver" = NULL)

## -----------------------------------------------------------------------------
#  odbc <- dbConnect(odbc::odbc(), dsn = "MicrosoftSQLServer", uid = "SA", pwd = "BoopBop123!")
#  rodbc <- RODBC::odbcConnect(dsn = "MicrosoftSQLServer", uid = "SA", pwd = "BoopBop123!")
#  rodbcdbi <- dbConnect(RODBCDBI::ODBC(), dsn = "MicrosoftSQLServer", user = "SA", password = "BoopBop123!")

## ----include = FALSE----------------------------------------------------------
#  if ("flights" %in% dbListTables(odbc)) {
#    dbExecute(odbc, "drop table flights")
#  }

## -----------------------------------------------------------------------------
#  odbc_write <- system.time(dbWriteTable(odbc, "flights", flights))
#  
#  odbc_write

## ----include = !eval_timings--------------------------------------------------
#  #>   user  system elapsed
#  #>  0.883   0.176   8.108

## ----include = FALSE----------------------------------------------------------
#  if ("flights" %in% dbListTables(odbc)) {
#    dbExecute(odbc, "drop table flights")
#  }

## -----------------------------------------------------------------------------
#  rodbcdbi_write <- system.time(dbWriteTable(rodbcdbi, "flights", flights))
#  
#  rodbcdbi_write

## ----include = !eval_timings--------------------------------------------------
#  #>   user  system elapsed
#  #>  8.287  11.107 257.841

## ----include = FALSE----------------------------------------------------------
#  if ("flights" %in% dbListTables(odbc)) {
#    dbExecute(odbc, "drop table flights")
#  }

## -----------------------------------------------------------------------------
#  rodbc_write <- system.time(sqlSave(rodbc, flights, "flights"))
#  
#  rodbc_write

## ----include = !eval_timings--------------------------------------------------
#  #>   user  system elapsed
#  #>  8.266  11.023 235.825

## ----include = FALSE----------------------------------------------------------
#  if ("flights" %in% dbListTables(odbc)) {
#    dbExecute(odbc, "drop table flights")
#  }

## ----include = FALSE----------------------------------------------------------
#  if (!"flights" %in% dbListTables(odbc)) {
#    dbWriteTable(odbc, "flights", flights)
#  }

## -----------------------------------------------------------------------------
#  odbc_read     <- system.time(result <- dbReadTable(odbc, "flights"))
#  rodbcdbi_read <- system.time(result <- dbReadTable(rodbcdbi, "flights"))
#  rodbc_read    <- system.time(result <- sqlFetch(rodbc, "flights"))

## -----------------------------------------------------------------------------
#  odbc_read

## ----include = !eval_timings--------------------------------------------------
#  #>   user  system elapsed
#  #>  0.515   0.024   0.557

## -----------------------------------------------------------------------------
#  rodbcdbi_read

## ----include = !eval_timings--------------------------------------------------
#  #>   user  system elapsed
#  #>  1.308   0.035   1.356

## -----------------------------------------------------------------------------
#  rodbc_read

## ----include = !eval_timings--------------------------------------------------
#  #>   user  system elapsed
#  #>  1.291   0.033   1.343