0

I am working on a R shiny project where I need to read SQL tables from my Shiny application. I have tried using RODBC and RJDBC packages for the same and have found RJDBC to be faster. But it is still taking a lot of time to read.

Below is the code that I have used:

LocationOfJDBC <- "/usr/lib/sqlserver_jdbc/sqljdbc_4.1/enu/jre7/sqljdbc41.jar"
options(java.parameters = "-Xmx8048m")
drv <- JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver" ,  LocationOfJDBC ,identifier.quote="`")

df<-dbGetQuery(mydb, "Select char1,char2,char3,int4,date5 from table1")

This above query is taking around 90 sec to read 2.8 million rows and 5 columns (3 character, 1 int and 1 date column).

Is there a faster way to read SQL table in R??

kawsleo
  • 560
  • 4
  • 23
  • 4
    I don't think its a good idea to be reading this amount of data into `shiny`. Just get the things you need using aggregation capabilities of SQL – Pork Chop Apr 08 '19 at 13:14

1 Answers1

0

Assuming that you have already tried obvious solutions like proper indexing, I would suggest saving your large sql tables into a fast file transfer format on disk. Use the package fst if you need querying capabilities on your tables, or package feather if not. There are various threads comparing the two packages. My preferred format is fst as it allows querying from the disk. 

install.packages("fst")
library(fst)
?fst

Ideally, unless your shiny server really needs all 2.8m rows, I would further suggest pulling data on an as-needed basis.

Knio
  • 13
  • 4