This appears to be an SQL issue. Look for a way to speed up your queries in Postgresql. I presume you haven't created an index on 'index', which means that every time you run your SELECT, Postgresql is forced to do a full table scan (not good). If the index doesn't solve the problem, look for some SQL help.
> -----Original Message----- > From: Tamas K Papp [mailto:[EMAIL PROTECTED] > Sent: Saturday, August 13, 2005 4:03 AM > To: R-help mailing list > Subject: [R] retrieving large columns using RODBC > > > Hi, > > I have a large table in Postgresql (result of an MCMC > simulation, with 1 > million rows) and I would like to retrive colums (correspond > to variables) > using RODBC. I have a column called "index" which is used to > order rows. > > Unfortunately, sqlQuery can't return all the values from a > column at once > (RODBC complains about lack of memory). So I am using the > following code: > > getcolumns <- function(channel, tablename, colnames, totalrows, > ordered=TRUE,chunksize=1e5) { > r <- matrix(double(0),totalrows,length(colnames)) > for (i in 1:ceiling(totalrows/chunksize)) { > cat(".") > r[((i-1)*chunksize+1):(i*chunksize)] <- as.matrix( > sqlQuery(channel, paste("SELECT", paste(colnames,collapse=", "), > "FROM", tablename, > "WHERE index <=", i*chunksize, > "AND index >", (i-1)*chunksize, > if (ordered) "ORDER BY index;" > else ";"))) > } > cat("\n") > drop(r) # convert to vector if needed > } > > to retrieve it in chunks. However, this is very slow -- > takes about 15 > minutes on my machine. Is there a way to speed it up? > > I am running Linux on a powerbook, RODBC version 1.1-4, R 2.1.1. The > machine has only 512 Mb of RAM. > > Thanks, > > Tamas > > ______________________________________________ > R-help@stat.math.ethz.ch mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide! > http://www.R-project.org/posting-guide.html > ______________________________________________ R-help@stat.math.ethz.ch mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html