On 5/20/2006 8:15 AM, Steve Miller wrote:
Though I'd question the prudence of doing mass database changes through R:
Other things equal, single updates in a loop should be slower than a
correlated update due to the performance costs of bind and execute (and
perhaps even parse). Also,
Though I'd question the prudence of doing mass database changes through R:
Other things equal, single updates in a loop should be slower than a
correlated update due to the performance costs of bind and execute (and
perhaps even parse). Also, updates are generally slower than inserts because
of
We have a PostgreSQL table with about 40 records in it. Using
either RODBC or RdbiPgSQL, what is the fastest way to update one (or a
few) column(s) in a large collection of records? Currently we're
sending sql like
BEGIN
UPDATE table SET col1=value WHERE id=id
(repeated thousands of
I was going to suggest sqlUpdate in RODBC, but it looks like that
function also uses the UPDATE command repeated nrow times.
A second strategy that I generally prefer because it does not require
RODBC (as much) and better supports transaction control is to first
create a temporary table with the
Your approach seems very inefficient - it looks like you're executing
thousands of update statements. Try something like this instead:
#---build a table 'updates' (id and value)
...
#---do all updates via a single left join
UPDATE bigtable a LEFT JOIN updates b
ON a.id = b.id
SET a.col1 = b.value;
On 5/19/2006 11:17 AM, Duncan Murdoch wrote:
We have a PostgreSQL table with about 40 records in it. Using
either RODBC or RdbiPgSQL, what is the fastest way to update one (or a
few) column(s) in a large collection of records? Currently we're
sending sql like
BEGIN
UPDATE table
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
Hadley,
There are several reasons that running one large load and one large
update would be significantly faster than thousands of individual
updates.
First, the time it takes to execute a query does not grow linearly with
the size of a query. That is, the statement: SELECT TOP 100 * FROM
table
On 5/19/2006 3:19 PM, hadley wickham wrote:
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
