I'm attempting to query a MySQL database on a webpage. Within my R script, I have 4 different "query" functions along with multiple calculations which will display statistical graphs to my webpage, all dependent on an "N" variable. I'm using PHP (using shell_exec) to call to R and send "N". I'm using the RMySQL & ggplot2 libraries in R.
Running my R script with just 1 basic query function (includes dbConnect(), dbGetQuery and on.exit(dbDisconnect()), then using png(), plot(), and dev.off() takes ~15 seconds to display the graph on my website.
With 2 functions and 2 plots, I haven't even had the patience to wait it out to see if it works since the load time is so long. The queries themselves are rather lengthy (could probably made easier through looping), but I've tested them to work through MySQL and I'm not sure how to avoid loop errors with SQL.
Could the long loading time be due to having dbConnect/dbDisconnect in each individual function? Should I only do this once in the script (i.e. create a new "connect" function and then call to the other functions from here)?
Is it the fact I'm running multiple and lengthy query requests? If that's the case, would it be better if I split each "query function" into individual R scripts, then "shell_exec" each and allow the user to select which graphs to display (i.e. check boxes in HTML/PHP that allow for the execution of each script/graph desired)?
Through testing, I know that my logic is there, but I might be totally missing something. I would like to speed up the process so the website user doesn't have to stare at a loading screen forever and I can actually get some tangible results.
Sorry for the lengthy request, I appreciate any help you can give! If you'd like to see the webpage or any of my code to get a better idea, I can upload that and share.
Thanks!
EDIT: It should also be noted that I'm using a while loop (x < 100) for some of the calculations; I know loops in R are typically known to be expensive processes but the whole vectoring thing (I think that's the name?) is over my head.
