From d.wall@computer.org Tue Mar  13 00:18:28 2001
Received: from mail1.expertrade.com (r87-43-dsl.sea.lightrealm.net
	[216.122.43.87])
	by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f2D0IRx03265
	for <pgsql-interfaces@postgresql.org>;
	Mon, 12 Mar 2001 19:18:28 -0500 (EST)
	(envelope-from d.wall@computer.org)
Received: from reggae (reggae.expertrade.com [216.122.43.90])
	by mail1.expertrade.com (8.9.3/8.9.3) with SMTP id QAA10091
	for <pgsql-interfaces@postgresql.org>; Mon, 12 Mar 2001 16:21:03 -0800
Message-ID: <001e01c0ab53$1c6e4d80$5a2b7ad8@expertrade.com>
Reply-To: "David Wall" <d.wall@computer.org>
From: "David Wall" <d.wall@computer.org>
To: <pgsql-interfaces@postgresql.org>
Subject: JDBC autocommit versus own commits performance
Date: Mon, 12 Mar 2001 16:18:21 -0800
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_001B_01C0AB10.0E18B2E0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
X-Archive-Number: 200103/106
X-Sequence-Number: 1267

This is a multi-part message in MIME format.

------=_NextPart_000_001B_01C0AB10.0E18B2E0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I'm using Postgresql 7.1beta4 with JDBC, and I was wondering if I should go=
 to the trouble of modifying my connection pool to manage two pools, once w=
ith setAutoCommit(false) and the other with setAutoCommit(true).

As it is, I turne off auto commit since in my transaction processing, I nee=
d to control the commit/rollback.

But, there are also a lot of cases where I'm doing a simple query and I don=
't need transaction isolation, per se.  Is the performance better if I use =
auto-commit than if I SELECT and do a commit() myself? And is there any ben=
efit to doing a rollback on a select instead of a commit, since nothing cha=
nged?

Would there be a benefit with auto commit off if I had to do several differ=
ent SELECTs, since I'd only have to do one commit at the end instead of the=
 autocommits after each step?

Thanks,
David

------=_NextPart_000_001B_01C0AB10.0E18B2E0
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 5.50.4611.1300" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>I'm using Postgresql 7.1beta4 with JDBC, a=
nd I was=20
wondering if I should go to the trouble of modifying my connection pool to=
=20
manage two pools, once with setAutoCommit(false) and the other with=20
setAutoCommit(true).</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>As it is, I turne off auto commit since in=
 my=20
transaction processing, I need to control the commit/rollback.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>But, there are also a lot of cases where I=
'm doing=20
a simple query and I don't need transaction isolation, per se.&nbsp; Is the=
=20
performance better if I use auto-commit than if I SELECT and do a commit()=
=20
myself? And is there any benefit to doing a rollback on a select instead of=
 a=20
commit, since nothing changed?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Would there be a benefit with auto commit =
off if I=20
had to do several different SELECTs, since I'd only have to do one commit a=
t the=20
end instead of the autocommits after each step?<BR></FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Thanks,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>David</DIV></FONT></BODY></HTML>

------=_NextPart_000_001B_01C0AB10.0E18B2E0--




From gunnar@candleweb.no Tue Mar  13 11:53:17 2001
Received: from mail1.hub.org (webmail.hub.org [216.126.85.1])
	by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f2DBrHx04380;
	Tue, 13 Mar 2001 06:53:17 -0500 (EST)
	(envelope-from gunnar@candleweb.no)
Received: from thor.candleweb.no (thor.candleweb.no [195.139.160.194])
	by mail1.hub.org (8.11.1/8.11.1) with ESMTP id f2DBrGd04372;
	Tue, 13 Mar 2001 06:53:16 -0500 (EST)
	(envelope-from gunnar@candleweb.no)
Received: (from gunnar@localhost) by thor.candleweb.no (8.10.0/8.10.0/on4)
	id <f2DBcq211818>; Tue, 13 Mar 2001 12:38:52 +0100 (MET)
To: "David Wall" <d.wall@computer.org>
Cc: <pgsql-interfaces@postgresql.org>, pgsql-jdbc@postgresql.org
Subject: Re: JDBC autocommit versus own commits performance
References: <001e01c0ab53$1c6e4d80$5a2b7ad8@expertrade.com>
Mime-Version: 1.0 (generated by tm-edit 7.106)
Content-Type: text/plain; charset=US-ASCII
From: Gunnar R|nning <gunnar@candleweb.no>
Date: 13 Mar 2001 12:38:51 +0100
In-Reply-To: "David Wall"'s message of "Mon, 12 Mar 2001 16:18:21 -0800"
Message-ID: <x6puflucdw.fsf@thor.candleweb.no>
Lines: 32
X-Mailer: Gnus v5.4.65/Emacs 19.34
X-Archive-Number: 200103/111
X-Sequence-Number: 1272

[CC to the jdbc list]

"David Wall" <d.wall@computer.org> writes:

> [1  <text/plain; iso-8859-1 (quoted-printable)>]
> I'm using Postgresql 7.1beta4 with JDBC, and I was wondering if I should go to the trouble of modifying my connection pool to manage two pools, once with setAutoCommit(false) and the other with setAutoCommit(true).
> 
> As it is, I turne off auto commit since in my transaction processing, I need to control the commit/rollback.
> 
> But, there are also a lot of cases where I'm doing a simple query and I don't need transaction isolation, per se.  Is the performance better if I use auto-commit than if I SELECT and do a commit() myself? And is there any benefit to doing a rollback on a select instead of a commit, since nothing changed?
> 
> Would there be a benefit with auto commit off if I had to do several different SELECTs, since I'd only have to do one commit at the end instead of the autocommits after each step?
> 


I've been wondering the same thing myself, but haven't gotten around to
test performance implications of different configurations. If you do any
testing, it would be nice if you could share your data. 

Having two pools implies that you either have to use more backend processes
though(if you don't reduce the size of the pools). This may or may not be
an issue depending on the OS of your backend. 

You could of course also just create different checkout methods for the
same underlying pool, but that would be mostly for convenience and not
performance - although it could be OK if your updates are few compared to
your clean queries. 

regards, 

	Gunnar