Message-ID: <45759726.9060004@aeccom.com>
Date: Tue, 05 Dec 2006 16:58:30 +0100
From: Sven Geisler <sgeisler@aeccom.com>
Organization: AEC/communications GmbH
User-Agent: Thunderbird 1.5.0.8 (Windows/20061025)
MIME-Version: 1.0
To: Heikki Linnakangas <heikki@enterprisedb.com>
CC: pgsql-performance@postgresql.org
Subject: Re: single transaction vs multiple transactions
References: <45758FBF.2030404@aeccom.com> <457590A3.2090006@enterprisedb.com>
	<457591B7.80408@aeccom.com> <45759381.40909@enterprisedb.com>
In-Reply-To: <45759381.40909@enterprisedb.com>
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: 7bit

Hi,

Many thanks for the fast response.
I will use temporary table with copy.
Other issue I have is the connection between app and db.
I guess, the approach with the copy will also hold down the network I/O
between app and db. Keeping in mind that I produce 10,000+ statements.

Thx
Sven

Heikki Linnakangas schrieb:
> Sven Geisler wrote:
>> I have to insert my arguments to a temporary table first, because the
>> arguments are only known in the application tier.
>> Is a multiple insert to a temporary table and a final 'DELETE FROM x
>> WHERE y IN (SELECT z FROM tmp)' faster than multiple deletes?
> 
> If the number of records is high, it most likely is faster. You should
> try it with your data to be sure, but in general doing all the deletes
> in one batch is faster when the number of records is high because it
> allows using efficient merge joins or sequential scans.
> 
> Populating the temporary table with no indexes should be quite
> inexpensive if you make sure you don't do it one record at a time. Use
> the COPY command or batched inserts instead.
> 
>