Perl DBI, PostgreSQL performance question

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Been offline for a while, so I can't tell for sure if this was
addressed before when someone posted a similar "eval and catch"
scheme for Perl DBI (and specifically for DBD::Pg).

The short of it is, you do not need to use evals. Ever! Just
set commit off when connecting (AutoCommit => 0), and postgres
will not commit until you specifically tell it to.
On any error* it will rollback. If the script exits normally
without $dbh->commit() being called, it will rollback.

*any error that causes the script to die or otherwise prevents
the $dbh->commit() statement from being called, to be precise.

Here is a short example:

#!perl

use DBI;

my $DBNAME = "test";
my $DBPORT = 5432;
my $DBUSER = "thelma";
my $DBPASS = "lcB%g^22_db nM";

my $dbh = DBI->connect("dbi:Pg:dbname=$DBNAME;port=$DBPORT",
$DBUSER, $DBPASS,
{ AutoCommit=>0, RaiseError=>0, PrintError=>0})
or &Error(1); ## Custom error handling, eventually calls 'die'

my $NEWUSER_SQL =
"INSERT INTO users (uid,first,last,shoesize) VALUES (?,?,?,?)";
my $newuser_h = $dbh->prepare($NEWUSER_SQL)
or &Error(1, $NEWUSER_SQL);
my @sqlargs = (12,"wanda", "mae", "9 3/4");
$newuser_h->execute(@sqlargs)
or &Error(1, $NEWUSER_SQL, \@sqlargs);

## If the script died at any point above, "wanda mae" will not
## be in the database. If the "exit" line below is uncommented,
## "wanda mae" will not be in the database.

#exit;

$dbh->commit(); ## Now she is in there!

$dbh->rollback(); ## This has no effect whatsoever

Note that not all the DBD modules are guaranteed to exhibit
the "rollback on failure" feature, but luckilty for us,
postgresql does. :)

HTH,

Greg Sabino Mullane
greg@turnstep.com
PGP Key: 0x14964AC8 200112141116

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE8GiYtvJuQZxSWSsgRAj76AKCi4bMv/a7J07hSbZ/b0WQwL3BCRwCgvKtZ
CXAbjr5OzR+mxU0wE8Pz0vE=
=Wf40
-----END PGP SIGNATURE-----

Hi,

a Perl compiler optimizes. Does anybody know what really happens while
it optimizes? I think, both parts may be optimized (nearly) the same
way. so the resulting bytecode might be rather the same...

Correct me, if I am wrong.

mfg Frank Finner

On 14-Dec-01 Benjamin Franks sat down, thought for a long time and then
wrote:

I'm using the Perl DBI to interact with my PostgreSQL 7.1.3 database.
I had a section of code that looked something like the
following (it's only pseudocode):

**************
foreach
foreach
eval {
prepare first select statement;
execute first select;
prepare first update or insert;
execute first update or insert;

prepare second select statement;
execute second select;
prepare second update or insert;
execute second update or insert;

commit;
};
if ($@) {
rollback;
}
}
}
***************

I realized some of those statements did not need to be inside the
loops
and so figure if I changed the code to the following, it would speed
up:

***************
prepare first select statement;
prepare first update;
prepare first insert;
foreach
eval {
execute first select statement;
execute first update or insert;
commit;
};
if ($@) {
rollback;
next;
}

prepare second select statement;
prepare second update;
prepare second insert;

foreach
eval {
execute second select;
execute second update or insert;
commit;
};
if ($@) {
rollback;
}
}
}
***************

The results are the same in the database either way. From what I can
tell, it did not speed up. In fact it actually slowed
down. The SQL statements haven't changed at all and I haven't
changed the database schema, version, configuration options,
etc.. I would have imagined the second sequence would have been much
faster because needless SQL isn't being done inside the
inner loops. Does anyone have any ideas as to why this would be the
case? Could it have to do with moving from a single eval
block to two eval blocks with some statements outside the eval? What
about multiple commits--could they be expensive
operations?

I appreciate any info you may be able to provide.
Thanks,
--Ben

----------------------------------------------------------------------
---
"From causes which appear similar, we expect similar effects. This
is the
sum total of all our experimental conclusions." --David Hume

---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

--
Frank Finner

And now there is no turning back at all.
(M. Moorcock, "Elric Of Melnibone")"

I don't know the internals, but I would honestly *hope* that if two separate
commits are issued in the source code, the database will see two separate
commits. My guess is that the difference in execution times isn't so much the
difference in how fast the perl executes, but in the overhead that comes with
committing logs of separate transactions versus one big transaction (second
example versus first example, respectively). With that in mind, it might be
helpful to know generally how many times the various loops are expected to
iterate, at least in terms of order of magnitude.

Frank Finner <postgresql%finner.de@interlock.lexmark.com> on 12/14/2001 04:50:47
PM

To: Benjamin Franks <benjamin%dzhan.com@interlock.lexmark.com>
cc: pgsql-general%postgresql.org@interlock.lexmark.com,
dbi-users%perl.org@interlock.lexmark.com (bcc: Wesley
Sheldahl/Lex/Lexmark)
Subject: Re: [GENERAL] Perl DBI, PostgreSQL performance question

Hi,

a Perl compiler optimizes. Does anybody know what really happens while
it optimizes? I think, both parts may be optimized (nearly) the same
way. so the resulting bytecode might be rather the same...

Correct me, if I am wrong.

mfg Frank Finner

On 14-Dec-01 Benjamin Franks sat down, thought for a long time and then
wrote:

I'm using the Perl DBI to interact with my PostgreSQL 7.1.3 database.
I had a section of code that looked something like the
following (it's only pseudocode):

**************
foreach
foreach
eval {
prepare first select statement;
execute first select;
prepare first update or insert;
execute first update or insert;

prepare second select statement;
execute second select;
prepare second update or insert;
execute second update or insert;

commit;
};
if ($@) {
rollback;
}
}
}
***************

I realized some of those statements did not need to be inside the
loops
and so figure if I changed the code to the following, it would speed
up:

***************
prepare first select statement;
prepare first update;
prepare first insert;
foreach
eval {
execute first select statement;
execute first update or insert;
commit;
};
if ($@) {
rollback;
next;
}

prepare second select statement;
prepare second update;
prepare second insert;

foreach
eval {
execute second select;
execute second update or insert;
commit;
};
if ($@) {
rollback;
}
}
}
***************

The results are the same in the database either way. From what I can
tell, it did not speed up. In fact it actually slowed
down. The SQL statements haven't changed at all and I haven't
changed the database schema, version, configuration options,
etc.. I would have imagined the second sequence would have been much
faster because needless SQL isn't being done inside the
inner loops. Does anyone have any ideas as to why this would be the
case? Could it have to do with moving from a single eval
block to two eval blocks with some statements outside the eval? What
about multiple commits--could they be expensive
operations?

I appreciate any info you may be able to provide.
Thanks,
--Ben

----------------------------------------------------------------------

[snip]
--
Frank Finner

Perl is an interpreted language. It does not convert into byte code.

It is scanned each time you can it so the interpreter has to go through
everything line by line. There is no template that is run such as when you
write a plpgsql function

Hope this helps

Darren

Darren Ferguson
Software Engineer
Openband

On Fri, 14 Dec 2001, Frank Finner wrote:

Darren Ferguson <darren@crystalballinc.com> writes:

Perl is an interpreted language. It does not convert into byte code.

Not true. You never see the byte code (it's more like an internal
tree I think as with pl/pgsql) but it's there. See the docs.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863

At 07:35 AM 12/14/01 -0800, Benjamin Franks wrote:

I'm using the Perl DBI to interact with my PostgreSQL 7.1.3 database. I

had a section of code that looked something like the

following (it's only pseudocode):

...

block to two eval blocks with some statements outside the eval? What

about multiple commits--could they be expensive

operations?

Yep commits are expensive.

Reducing the number of commmits needed might speed things up.

Good luck,
Link.

According to "Programming Perl", Preface:

"Unlike a strictly interpreted language such as the shell, which
compiles and executes a script at one command at a time, Perl first
compiles your whole program quickly into an intermediate format. Like
any other compiler, it performs various optimizations... Once Perl�s
compiler frontend is happy with your program, it passes off the
intermediate code to the interpreter to execute..."

mfg Frank Finner

On 14-Dec-01 Darren Ferguson sat down, thought for a long time and then
wrote:

Perl is an interpreted language. It does not convert into byte code.

It is scanned each time you can it so the interpreter has to go
through
everything line by line.

--
Frank Finner

And now there is no turning back at all.
(M. Moorcock, "Elric Of Melnibone")"

Thanks for the clarification.

I assumed perl was what i assumed it to be. Thnaks for putting me on the
right wavelength :-)

Darren

Darren Ferguson
Software Engineer
Openband

On Sat, 15 Dec 2001, Frank Finner wrote: