On Thu, Dec 1, 2011 at 10:02 AM, Berry, David I. <d...@noc.ac.uk> wrote:
> 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)
If this is a large table of which the desired rows are a small
fraction of all rows then be sure there indexes on the variables in
your where clause.
You can also try it with the RpgSQL driver although there is no reason
to think that that would be faster.
--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
______________________________________________
R-help@r-project.org 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.
