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.