From: Markus Schaber <schabios@logi-track.com>
To: ischamay.andbergsay@activestateway.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Data Warehouse Reevaluation - MySQL vs Postgres --
Date: Thu, 16 Sep 2004 12:39:04 +0200
Message-ID: <20040916123904.3ddb8cd6@kingfisher.intern.logi-track.com> (raw)
In-Reply-To: <gIK1d.26646$XP3.14809@edtnps84>
References: <20040912042442.49915.qmail@web41508.mail.yahoo.com>
<pB21d.193816$X12.122954@edtnps84>
<20040914141452.002e5d4e@kingfisher.intern.logi-track.com>
<20040914143303.GR21419@ns.snowman.net>
<gIK1d.26646$XP3.14809@edtnps84>
Hi, Mischa,
On Tue, 14 Sep 2004 22:58:20 GMT
Mischa Sandberg <ischamay.andbergsay@activestateway.com> wrote:
> Googling 'upsert' (an Oraclism, I believe) will get you hits on Oracle
> and DB2's implementation of MERGE, which does what AMOUNTS to what is
> described below (one mass UPDATE...FROM, one mass INSERT...WHERE NOT
> EXISTS).
>
> No, you shouldn't iterate row-by-row through the temp table.
> Whenever possible, try to do updates in one single (mass) operation.
> Doing it that way gives the optimizer the best chance at amortizing
> fixed costs, and batching operations.
But when every updated row has a different value for the column(s) to be
updated, then I still have to use one update statement per row, which I
expect to be faster when done via a stored procedure than having the
whole client-server roundtrip including parsing every time. Or did I
miss some nice SQL statement?
Have a nice day,
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: schabios@logi-track.com, ischamay.andbergsay@activestateway.com
Subject: Re: Data Warehouse Reevaluation - MySQL vs Postgres --
In-Reply-To: <20040916123904.3ddb8cd6@kingfisher.intern.logi-track.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox