Hi there ... In the past I've had some difficulty inserting some larger tables into postgres using dbWriteTable() from RdbiPgSQL, in terms of speed performance but now I've run into some data.frames which are just too large (although they don't seem to be all that large in practical terms). As an example, I was attempting to insert the assayData from two SnpCallSets into a DB over the weekend, 6 tables each of ~350 columns and 250K rows. When I came back this morning (~3.5 days), it hadn't even completed one of these tables, with both CPU and RAM maxxed (on a machine w/ 16GB of RAM and a pretty decent CPU). Anyone have ideas on a faster method to get these tables inserted? One possibility that I've found in the past to be quicker is to manually loop INSERT statements, but the problem there is detection of data types (which I believe is the real slowdown from the RdbiPgSQL package in the first place) and proper quoting/non-quoting.

Jeff Gentry wrote: > Hi there ... > > In the past I've had some difficulty inserting some larger tables into > postgres using dbWriteTable() from RdbiPgSQL, in terms of speed > performance but now I've run into some data.frames which are just > too large (although they don't seem to be all that large in practical > terms). > > As an example, I was attempting to insert the assayData from two > SnpCallSets into a DB over the weekend, 6 tables each of ~350 columns and > 250K rows. When I came back this morning (~3.5 days), it hadn't even > completed one of these tables, with both CPU and RAM maxxed (on a machine > w/ 16GB of RAM and a pretty decent CPU). > > Anyone have ideas on a faster method to get these tables inserted? One > possibility that I've found in the past to be quicker is to manually loop > INSERT statements, but the problem there is detection of data types (which > I believe is the real slowdown from the RdbiPgSQL package in the first > place) and proper quoting/non-quoting. Hi, Jeff. The fastest way to get data into postgresql is to use some version of the copy protocol. I do not know what RdbiPgSQL uses for doing dbWriteTable. I did a quick test on my machine (4Gb RAM, 3-year-old Mac G5) and was able to insert 250,000 rows by 350 columns of floats in about 2 minutes. In order to do this: 1) Create the table. You can generate the create statements in R using the class of the various columns of your dataframe and combinations of paste. Alternatively, use dbWriteTable with a couple of rows to get the table creation done automatically. If you use dbWriteTable, do not forget to do a TRUNCATE before loading data. 2) Write your data.frame to disk, tab-delimited text, no column names, with NULL values as "\N" 3) start up psql and do: \copy tablename from filename Generally, looping over inserts (or any form of insert statement) will be much slower than using "copy". Also, make sure that you drop indexes before using copy (or doing inserts, for that matter)--these will kill you on a large insert to a large table. If you do need to do inserts, be sure to issue a "begin" before doing the inserts and a "commit" at the end; isolating things in a transaction block will gain you speed. Hope that helps. Sean

> >Yup, your method is essentially what I came across yesterday afternoon, >with some slight variations. What you outline is a bit better >though. This works for my immediate task, although I'm going to need a >way to allow this to be automated (hopefully within RdbiPgSQL >itself). COPY won't work, and /copy of course needs to be called from >psql. One thought was to try and get /copy implemented in R. > Hi, jeff, Have you tried to do copy in plain sql within RdbiPgSQL? e. g. > sql <- "copy yourDBName from 'yourFileName' using delimiters 'yourDelimiters'" > dbSendQuery(yourConnection, sql) >_______________________________________________ >Bioconductor mailing list >Bioconductor at stat.math.ethz.ch >https://stat.ethz.ch/mailman/listinfo/bioconductor >Search the archives: http://news.gmane.org/gmane.science.biology.informatics.conductor Jianhua Zhang Department of Medical Oncology Dana-Farber Cancer Institute 44 Binney Street Boston, MA 02115-6084