I dont need to load the entire dataset into memory. In fact I only need 1 row at a time to apply a trained model, get the predicted response and put that response somewhere, possibly back into another table in the DB. Question is, how to do this efficiently. Of course the entire SQL based DB wont fit into RAM, but a few million rows of it will at a time. Say the entire DB is a billion rows. Is there any package which efficiently retrieves the data, applies a model or runs a function on the data and repeats in a parallel way, utilizing all cores, RAM (to minimize number of over the network pulls), etc? Thank you.


You should probably start by familiarizing yourself with the High Performance Computing Task View.

The final answer could depend on several things that you have not told us.

If this is a simple linear model then you could simply construct a select statement in SQL to pass to the database that multiplies each variable by its slope and adds the results (and the intercept) and either inserts this as a new column in the table or returns the values for the subset of interest.

If your model is more complicated and you want to do the prediction using R functions then you might need to move your data into something like an ff object (see the ff package) which stores the data on disk and brings only parts into memory at a time and provides apply like functions to process the whole dataset in chunks.

There are other tools on that Task View that may be more appropriate for what you want to do.

  • $\begingroup$ Greg, thank you. The question is really about the later case, where the data needs to be brought into R, a prediction model applied and the results stored. There is actually no need for ff. The process is Fetch data from ODBC into a data frame-> Apply model -> Write results to disk -> Repeat. The challenge is, moving through that flow in the most efficient way possible. Is there any code / packages out there which specialize in doing this? $\endgroup$ Jan 27 '15 at 18:40
  • 1
    $\begingroup$ @GeneEkster, look at the Rdsm package and documentation. I have not used it much, but I remember reading some things in the documentation that seem similar to the parallelization that you ask about. If that does not help, then I would look through other options on the task view. The dplyr package may be another option for interfacing with the database and pulling in data in chunks (I don't know if it will work in parallel or not). $\endgroup$
    – Greg Snow
    Jan 27 '15 at 18:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.