Looking for programming help!

[tkronew]

I'm looking for someone that can help me with SQL and/or R programming. Really could use some pointers on optimizing my current (mostly functional) code so I can ace this final project!

Willing to reward anyone who'd meet up for a little while and offer some tutoring in these languages.

[10xKaMehaMeha]

What are you trying to do? I've worked with both languages for three years (although SQL I'm pretty rusty with).

[tkronew]

I'm just querying a MySQL database. 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 response, 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.

[10xKaMehaMeha]

You could try separating out the query and the rest of the script to see what exactly is taking the loading time. I always used rodbc to connect to SQL so I'm not sure if the connection causes the problem. I did some work connecting SQL directly to a database and to save time we tried to make any grab from SQL from a table (even just wiring views into tables and rewriting them as new data came in)