From: Richard Huxton <dev@archonet.com>
To: Joel Fradkin <jfradkin@wazagua.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Speeds using a transaction vrs not
Date: Thu, 24 Feb 2005 14:21:04 +0000
Message-ID: <421DE2D0.9070601@archonet.com> (raw)
In-Reply-To: <001b01c51a77$f704e250$797ba8c0@jfradkin>
References: <001b01c51a77$f704e250$797ba8c0@jfradkin>
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
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-sql@postgresql.org
Cc: dev@archonet.com, jfradkin@wazagua.com
Subject: Re: Speeds using a transaction vrs not
In-Reply-To: <421DE2D0.9070601@archonet.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