Hi List
Apologies if this isn't the correct place for this query (I've tried a search
of the mail archives but not had much joy).
I'm running R (2.14.0) on a Mac (OSX v 10.5.8, 2.66GHz, 4GB memory) and am
having a few performance issues with reading data in from a Postres database
(using RPostgreSQL). My query / code are as below
# -----------------------------
library('RPostgreSQL')
drv <- dbDriver("PostgreSQL")
dbh <- dbConnect(drv,user="
",password="
",dbname="
",host="
")
sql <- "select id, date, lon, lat, date_trunc('day' , date) as jday,
extract('hour' from date) as hour, extract('year' from date) as year from
observations where pt = 6 and date >= '1990-01-01' and date < '1995-01-01' and
lon > 180 and lon < 290 and lat > -30 and lat < 30 and sst is not null"
dataIn <- dbGetQuery(dbh,sql)
# -----------------------------
All variables are reals other than id which is varchar(10) and date which is a
timestamp, approximately 1.5 million rows are returned by the query and it
takes order 10 second to execute using psql (the command line client for
Postgres) and a similar time using pgAdmin 3. In R it takes several minutes to
run and I'm unsure where the bottleneck is occurring. I don't believe it's in
the database access as the query disappears from the server status (of pgAdmin)
quite quickly and where it only takes ~10 seconds from psql. I've only been
using R for the last month or two so any tips / advice on how to speed up the
query or how to track down the source of the poor performance would be
appreciated.
For info, I get similar poor performance running R (2.14.0) on a linux
workstation (Linux 2.6.16.60-0.91.1-smp x86_64, 42GB memory, 2 6 core
processors) running SUSE.
Thanks in advance
Dave Berry.
--
This message (and any attachments) is for the recipient ...{{dropped:8}}
______________________________________________
[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
and provide commented, minimal, self-contained, reproducible code.
