Thread: Perl DBI, PostgreSQL performance question
I'm using the Perl DBI to interact with my PostgreSQL 7.1.3 database. I had a section of code that looked something
likethe
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
insidethe
inner loops. Does anyone have any ideas as to why this would be the case? Could it have to do with moving from a
singleeval
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
-----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:
> 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")"
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
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: > 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")" >