1

I use DBI and RMySQL package to import the whole table from the database. The code works as expected. I would like to know is there a faster way to import the same table multiple times? For example, I import the table, do some calculations, close the R session, and then import the same table again tomorrow. Is there a way to somehow cache that table and import the same table in a faster way?

The code example (working as expected):

library(RMySQL)
library(DBI)

# coonect to database
connection <- function() {
  con <- DBI::dbConnect(RMySQL::MySQL(), 
                        host = "91.234.xx.xxx", 
                        port = 3306L,
                        dbname = "xxxx",
                        username = "xxxx",
                        password = "xxxx",
                        Trusted_Connection = "True")
}

# imoprt
db <- connection()
vix <- DBI::dbGetQuery(db, 'SELECT * FROM VIX')
invisible(dbDisconnect(db))
zx8754
  • 52,746
  • 12
  • 114
  • 209
Mislav
  • 1,533
  • 16
  • 37
  • How big is the db? Is it static, or changes everyday? Maybe a [related post, using data.table instead of mysql](https://stackoverflow.com/q/6590630/680068). – zx8754 Jun 16 '20 at 11:47
  • For now, it is static. Later I will maybe change once per day. The post explains I should use data.table, but the table is SQL table? – Mislav Jun 16 '20 at 11:54
  • Yes, linked post suggests reading from flat file using data.table is faster than mysql, as long as data can fit in memory. – zx8754 Jun 16 '20 at 12:09

0 Answers0