Message-ID: <421DE2D0.9070601@archonet.com>
Date: Thu, 24 Feb 2005 14:21:04 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
MIME-Version: 1.0
To: Joel Fradkin <jfradkin@wazagua.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Speeds using a transaction vrs not
References: <001b01c51a77$f704e250$797ba8c0@jfradkin>
In-Reply-To: <001b01c51a77$f704e250$797ba8c0@jfradkin>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Joel Fradkin wrote:
> No I did not do it in on transaction (although in .net I never started or
> commited a transaction.

All inserts/updates/etc take place within a transaction with PostgreSQL. 
Some client libraries autocommit for you - you'll need to read the 
documentation.

> ODBC :
> myCommand.CommandText = insertsqltext
> myCommand.ExecuteNonQuery()
> myTrans.Commit()
> 
> .net driver:
> Dim cmd As New NpgsqlCommand(insertsqltext, cnn)
> cmd.ExecuteNonQuery()
> cmd.Dispose()

Both look to me like they are producing one transaction per insert (the 
slowest possible way to bulk-copy data). That's assuming each block of 
commands is within one loop.

Precisely what is happening will be easiest to see by turning statement 
logging on in your postgresql.conf and comparing two runs. The delay 
might be in overheads of setting up the transaction etc. with the ODBC 
driver.

In any case, if bulk-copying data you'll find a huge improvement 
grouping rows together in batches of 100 - 10,000.

--
   Richard Huxton
   Archonet Ltd