Re: [R] Fast update of a lot of records in a database?

From: hadley wickham <h.wickham_at_gmail.com>
Date: Sat 20 May 2006 - 05:19:48 EST

> put the updates into a temporary table called updates
>
> UPDATE bigtable AS a
> FROM updates AS b
> WHERE a.id = b.id
> SET a.col1 = b.col1

I don't think this will be any faster - why would creating a new table be faster than updating existing rows?

I've never had a problem with using large numbers of SQL update statements (in the order of hundreds of thousands) to update a table and having them complete in a reasonable time (a few minutes). How heavily indexed is the field you are updating? You may be able to get some speed improvements by turning off indices before the update and back on again afterwards (highly dependent on your database system though).

I would strongly suspect your bottleneck lies elsewhere (eg. when generating the statements in R, or using ODBC to send them)

Hadley



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 Received on Sat May 20 05:24:09 2006

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.1.8, at Sun 21 May 2006 - 02:10:11 EST.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.