Date: Thu, 24 Feb 2005 10:47:50 -0500
From: "Joel Fradkin" <jfradkin@wazagua.com>
Subject: Re: Speeds using a transaction vrs not
In-reply-to: <421DE2D0.9070601@archonet.com>
To: "'Richard Huxton'" <dev@archonet.com>
Cc: <pgsql-sql@postgresql.org>
Message-id: <002f01c51a88$32fb1f80$797ba8c0@jfradkin>
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: quoted-printable

Thanks.

I guess I could add that logic, but this is a one time process going to =
run
the night we go live on postgres.
With .net it took like 2 hours to do the whole shebang, so I am happy.

But I was curious as we will be using odbc for our asp (aprx 90% of our
app).

I could test wrapping the .net in a transaction the way I do in odbc, =
but
you indicate it is doing that behind the scenes.

So far progress is going really great on our conversion.
I cant wait to see us up on the new platform.

I have not researched the details on doing autovacuum and backup.
I have tested manually backing up and restoring.

We are only like 10% done converting the code (I had hoped it going =
faster,
but I have not been hands on helping, so maybe it go faster when I get =
time
to start helping).

I did convert a fairly large app and it seemed to run faster on the =
postgres
box then the MSSQL box, so my first project after actually getting the =
data
and views has gone very smooth.

Everyone has been so helpful on the lists and that is the only reason I =
am
where I am so far so thanks again to everyone who has bothered to answer =
my
noob questions.

Joel Fradkin
=20
Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel.  941-753-7111 ext 305
=20
jfradkin@wazagua.com
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
 This email message is for the use of the intended recipient(s) and may
contain confidential and privileged information.  Any unauthorized =
review,
use, disclosure or distribution is prohibited.  If you are not the =
intended
recipient, please contact the sender by reply email and delete and =
destroy
all copies of the original message, including attachments.
=20

=20

-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]=20
Sent: Thursday, February 24, 2005 9:21 AM
To: Joel Fradkin
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Speeds using a transaction vrs not

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=20
documentation.

> ODBC :
> myCommand.CommandText =3D insertsqltext
> myCommand.ExecuteNonQuery()
> myTrans.Commit()
>=20
> .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=20
slowest possible way to bulk-copy data). That's assuming each block of=20
commands is within one loop.

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

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

--
   Richard Huxton
   Archonet Ltd