Re: Postgres and multiple updates in one statement

Hi There

Our update statements are as follows

Update table set col1=x,col2=y where pkid=1;
Update table set col1=x,col2=y where pkid=2;
Update table set col1=x,col2=y where pkid=3;

Very simple and straight forward. Sometimes there could be as many as 50
update statements to process.

Many thanks

Jamie

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of nha
Sent: 27 July 2009 14:35
To: Jamie Lawrence-Jenner
Cc: PgSQL-general
Subject: Re: [GENERAL] Postgres and multiple updates in one statement

Hello,

Le 27/07/09 15:07, Raymond O'Donnell a écrit :
> On 27/07/2009 09:10, Jamie Lawrence-Jenner wrote:
>
>> Apart from saving on the overhead of having to open up 5 separate
>> connections, what are the benefits to passing in multiple updates in one
>> statement?
>
> If you do them all within one transaction -
>
> begin;
> update....
> update...
> ...
> commit;
>
> - then you save on the overhead associated with beginning and committing
> a transaction for each update.
>

Next to the transaction way suggested by Raymond O'Donnell, I would add
that performance would depend on FROM and WHERE clauses specified in the
original UPDATE statements.

In the case of multiple UPDATE statements following a quite "similar"
schema (ie. similar FROM (optional) and WHERE clauses), it might be
clearer (for the source code) and faster (for the database engine) to
merge them in a single UPDATE statement. Otherwise (ie. UPDATE
statements with not so much FROM and/or WHERE clauses in common),
transaction 1-block statement as suggested by Raymond O'Donnell would
certainly be the more appropriate.

Improvements on the merging UPDATE statements may thence be advised if
some pieces of original statements could be given--without compromising
confidential data.

Regards.

--
nha / Lyon / France.