0

I have an SQLite DB that I am using in a shiny app. My queries work quite quickly when there are only one person that has the connection to the DB open, however when there are more than one person it becomes quite slow. The table I am querying is quite large. 870k rows by 67 columns. I'm a little confused why it lags so much when multiple people are using it though. Any thoughts / suggestions would be greatly appreciated.

poolfinderdatabase <- dbPool(drv = SQLite(), dbname = "NewDB")

output$poolfinderTS <- renderPlotly({

PF_TimesSeries <- tbl(poolfinderdatabase, "NewDB")%>%
                 filter( CPN %in% local(input$Coupon1),Story %in%   
                     local(input$PoolStories1), Coll %in%   
                     local(input$Agency1),WALA >=    
                     local(input$wala_sliders[1]) & WALA<=   
                     local(input$wala_sliders[2]),   
                     Current.Face >= 5000000)%>%  
                collect()%>%  
                mutate(Coll = gsub("FGLMC", "FNCL",Coll))%>%  
                group_by(Date,CPN,Story,Coll)%>%  
                summarize(Median = median(get(input$Y1),na.rm = TRUE))%>%  
                ungroup()%>%  
                mutate(Date =parse_date_time(x = Date, orders =   
                c("mdy","ymd")))%>%  
                mutate(Product = paste(Coll," ",CPN," ",Story))  

  TimeSeries_Chart <- ggplot(PF_TimesSeries,   
                    aes(Date,Median, group = 1,colour =  
                    Product, text=paste(  
                     "</br> Date:", PF_TimesSeries$Date,  
                     "</br> Value:", round(PF_TimesSeries$Median, 3),  
                     "</br> Product:",PF_TimesSeries$Product)))+  
                         geom_line()+  
                         theme_minimal()+  
                         theme_light()+  
                         scale_color_hue(l=40, c=80)  

 ggplotly(TimeSeries_Chart,tooltip = "text")  


})
akennedy12345
  • 31
  • 4
  • Please use [code-blocks](https://stackoverflow.com/editing-help). Further, your code is incomplete; at a minimum, is there anything else within the `renderPlotly`, or should there be a `})` after all of that dplyr-pipe? – r2evans Mar 18 '21 at 18:45
  • sorry about that. included the rest of the code and hopefully fixed the formatting a bit. I haven't posted too many times on here so apologies for the messy post. – akennedy12345 Mar 18 '21 at 18:52
  • 1
    Are you certain the slow-down is in the query and not in the shiny interface? If you're serving this in a single process, then each user's query will "lock up" R for a moment while it runs the query, and everybody's shiny interface may show artifacts of this. – r2evans Mar 18 '21 at 19:51
  • I am really not sure where the slow-down is occurring. currently the way we are using this is just running the shiny app individually from each of our computers. and then when two or more people have the app going at the same time it tends to lag. Do you mind explaining what you mean by "if you're serving this in a single process"? – akennedy12345 Mar 18 '21 at 19:57
  • In single-user mode (e.g., interactively from a personal R console), R is single-threaded for all users. One thread for all, not one thread each. This means that one "long running" operation of any kind will cause delays for everybody. Most things are fairly zippy, so several users can use a sample-hosting like this, but longer operations (including non-instantaneous queries) will be "felt" by all. Suggested reading: https://rstudio.github.io/promises/articles/casestudy.html and https://rstudio.github.io/promises/articles/shiny.html. – r2evans Mar 18 '21 at 20:00
  • 1
    Oh!! this is very interesting. Thank you so much. – akennedy12345 Mar 18 '21 at 20:24
  • In case you need more ... recently, Gábor Csárdi posted https://blog.r-hub.io/2021/03/13/rsqlite-parallel/ about RSQLite concurrency issues. News to me, hope it can help you too. – r2evans Mar 21 '21 at 04:29
  • @r2evans I ended up using the Future / promise packages from the resources you provided. however i am still experiencing slowness when i query this DB. it takes about 50 seconds when there is another user and 3 seconds when its just one person. – akennedy12345 Mar 26 '21 at 17:29
  • Can you reproduce the slowness by running `sqlite3` itself on two separate shells? (Outside of R.) I don't know if it is an R thing or SQLite. – r2evans Mar 26 '21 at 18:10

0 Answers0