On Mon, 15 Aug 2005, bogdan romocea wrote: > 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.
If that were the case the fact that sqlQuery is not being used properly (it can do the query and return the results in blocks) is likely to be the problem. But then we do ask people to read the help page before posting. > > >> -----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 >> >> ______________________________________________ >> [email protected] mailing list >> https://stat.ethz.ch/mailman/listinfo/r-help >> PLEASE do read the posting guide! >> http://www.R-project.org/posting-guide.html >> > > ______________________________________________ > [email protected] mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html > -- Brian D. Ripley, [EMAIL PROTECTED] Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/ University of Oxford, Tel: +44 1865 272861 (self) 1 South Parks Road, +44 1865 272866 (PA) Oxford OX1 3TG, UK Fax: +44 1865 272595 ______________________________________________ [email protected] mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
