R Notebook

In this Project,we are going to observe the time difference between querying the database with the SQL Syntax and querying the database with the dplyr syntax.

library(dplyr)
con <- dbConnect(RSQLite::SQLite(), dbname = ":memory:")
copy_to(con, nycflights13::flights, "flights",
        temporary = FALSE, 
        indexes = list(
          c("year", "month", "day"), 
          "carrier", 
          "tailnum",
          "dest"
        )
)

dbListTables(con)

[1] "flights"      "sqlite_stat1"

dbListFields(con, "flights")

 [1] "year"           "month"          "day"            "dep_time"      
 [5] "sched_dep_time" "dep_delay"      "arr_time"       "sched_arr_time"
 [9] "arr_delay"      "carrier"        "flight"         "tailnum"       
[13] "origin"         "dest"           "air_time"       "distance"      
[17] "hour"           "minute"         "time_hour"     

#Selecting the month 1 from dataset
start=proc.time()
request=dbSendQuery(con, "SELECT * FROM flights WHERE month = 1")

Closing open result set, pending rows

dbFetch(request)

sleep_for_a_minute <- function() { Sys.sleep(60) }
time=proc.time()-start
time

   user  system elapsed 
   0.50    0.02    0.60 

We will use elapsed time for data analysis. time=0.60 Sec Using Dplyr

library(nycflights13)
library(tidyverse)
start_1=proc.time()
filter(flights,month==1)

dplyr_filtertime=proc.time()-start_1
dplyr_filtertime

   user  system elapsed 
   0.27    0.00    0.30 

Time=0.30 sec

#Selecting the month 1 from dataset
start=proc.time()
request=dbSendQuery(con, "SELECT month, year, day FROM flights ")
dbFetch(request)

sleep_for_a_minute <- function() { Sys.sleep(60) }
time=proc.time()-start
time

   user  system elapsed 
   0.38    0.04    0.45 

Time=0.45 Sec

library(nycflights13)
library(tidyverse)
start_1=proc.time()
select(flights,month ,year,day)

dplyr_filtertime=proc.time()-start_1
dplyr_filtertime

   user  system elapsed 
   0.09    0.00    0.18 

Time=0.18 Sec

Selecting a value from column(SQL Syntax(Time in Sec)) 0.60

Selecting a value from column(dplyr Syntax(Time in Sec)) 0.30

Selecting 3 columns ((SQL Syntax(Time in Sec)) 0.45

Selecting 3 columns ((dplyr Syntax(Time in Sec)) 0.18