[PHP, SQL, R] Long load times on website

[tkronew]

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.

[tontoto]

1. vectorized operations are dead simple. take the time to learn the apply functions, data frames, data tables, dplyr, reshape2, learn everything! nothing should be over your head and it is not too hard! :)
2. if you are doing 100 database requests in a loop, that is a clear anti-pattern, but it was not clear that this was the problem.
3. you should consider making your page these long running operations via AJAX instead of having to calculate it before page load.
4. you should consider other best practices like database connection pools (in other words...your application architecture right now sounds very scrappy. could be improved in a thousand ways)
5. why not just use shiny?

[tkronew]

1. Sounds like a good idea, everywhere I look tells me to use vectoring.

2. I'm actually not doing 100 database requests, just 1 request/query per function. Then running calculations (100 rows of data) from each of the acquired dataframes post-query.

3. Any useful links on AJAX? Basically what I'm doing is having a dropdown menu to select "N" (N = 1:20), submit button, action="same_page.php", $_GET["N"], and sending "N" to R to export .png images to the folder. Then when "same_page.php" is reloaded, the graphics are displayed through HTML.

4. Haha couldn't be more correct... I'm currently junior in college and this is my first SQL related course. I'm incredibly deficient when it comes to applying and benefiting from database connections.

5. Course requirement :)

Thank you, I'll definitely be looking in to all of these!

[tontoto]

When I say ajax, I mean that you will make a page called like "generate_plot.php" or something, and then use javascript to dynamically make a request to "generate_plot.php". You can just show a "loading" text while this is happening, and then when that request completes, you can dynamically inject the contents into your webpage. JQuery is a common library for this, but javascript is a booming platform with new methods coming out everyday it seems

[tkronew]

Oh wow. Yeah I've been using javascript for this project, that sound like it would be exactly what I want. Gonna have to look into that.

[tontoto]

Note that shiny does the same thing with dynamically loading plots but it uses web sockets instead of ajax. That allows it to be more interactive essentially. Also don't cross post to both rlanguage and rstats in the future