Hi everybody,

I have a MySQL database with stock information (using beancounter: thanks
Kirk).
I return 30 stocks from the database (data.frame format by default)
I am converting the data.frame to xts format. In the code below,


"df"   is the data.frame returned from the database.
"data" is what I get after conversion of df to an xts object.


Here is the problem:

Getting 30 stocks from the data.base and return a data.frame takes 0.14
seconds
Including conversion to the more efficient xts structure, takes 2 seconds.

If I had 3000 stocks, it is the difference between 14 sec and 3 min, which
is quite large.
Is there a solution to this dilemma? Of course, I could recode the database
access functions.

One solution of course, is to convert to xts and save to a file, so that
conversion is no longer necessary, and I might do that if nobody comes up
with a more direct solution.


   Thanks,

       Gordon


------------------------------------------------------
I wrote the code below (which works):

library("RMySQL")
library("candlesticks")

db="beancounter"

con <- dbConnect(MySQL(), user="erlebach", dbname="beancounter")

get.stock = function(stock.sym, con)
{
cmd = paste("select symbol, date, day_open, day_high, day_low, day_close,
volume from stockprices where symbol='", stock.sym, "'", sep="")
return( dbGetQuery(con, cmd) )
}
#-------------------------------------------------

source("symbols.txt")

new_stocks = new_stocks[1:30]

all.stocks = function()
{
for (sym in new_stocks)
{
df = get.stock(sym, con)
# data <- xts(df[,3:7], order.by = as.Date(df$date), unique = T, tzone = "")
}
}

#all.stocks()
print(system.time(all.stocks()))

dbDisconnect(con)

	[[alternative HTML version deleted]]

