This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
To: Benjamin Franks <benjamin@dzhan.com>
From: Frank Finner <postgresql@finner.de>
Subject: RE: [GENERAL] Perl DBI, PostgreSQL performance
question
Date: Fri, 14 Dec 2001 22:50:47 +0100 (CET)
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):
>
> **************
> 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?
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.
===
To: Frank Finner <postgresql@finner.de>
From: Darren Ferguson <darren@crystalballinc.com>
Subject: Re: [GENERAL] Perl DBI, PostgreSQL performance
question
Date: Fri, 14 Dec 2001 17:37:08 -0500 (EST)
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
===
To: Darren Ferguson <darren@crystalballinc.com>
From: Doug McNaught <doug@wireboard.com>
Subject: Re: [GENERAL] Perl DBI, PostgreSQL performance
question
Date: 14 Dec 2001 18:02:05 -0500
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.
===
To: dbi-users@perl.org
From: "Michael Wray" <michaelw@staff.familyconnect.com>
Subject: RE: [GENERAL] Perl DBI, PostgreSQL performance
question
Date: Fri, 14 Dec 2001 18:58:08 -0600
To quote one of the WROX books I have been reading on EVAL (I don't know which)
Avoid using EVAL inside of a loop, rather EVAL the loop, as
EVAL will REINTERPERET with EVERY ITERATION...so..using an
EVAL inside of a loop causes a huge performance loss...and
it is doubly so in a nested loop.
Might I suggest using the DBI methods of retrieving ERROR
codes REALLY is faster...AND: (Since this is
postgres..PREPING and EXECUTING is useless...PG doesn't
understand PREPARE the driver just allows it since it is a
DBI call...DO is more practical here:
$query=" Select Statement;INSERT/UPDATE Statement;
$sth=$dbh->do(qq($query));
$err=$sth->err;
$result=$sth->result;
$rows=$sth->rows;
$errstr=$sth->errstr;
if ($result && ! $err) then { $sth->commit; } else {$sth->rollback;}
OR some such rot would be quicker and easier to debug SQL
issues with. (Please Double Check any syntax since it's a
COMBO of Pseudo Code and REAL code...(I usually have to have
the DBI book out for syntax..and don't have it available at
this time..)
===
===
To: Frank Finner <postgresql@finner.de>
From: wsheldah@lexmark.com
Subject: Re: [GENERAL] Perl DBI, PostgreSQL performance question
Date: Fri, 14 Dec 2001 17:23:39 -0500
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.
===
To: Michael Wray <michaelw@staff.familyconnect.com>
From: Rudy Lippan <almighty@randomc.com>
Subject: RE: [GENERAL] Perl DBI, PostgreSQL performance
question
Date: Fri, 14 Dec 2001 20:39:13 -0500 (EST)
> To quote one of the WROX books I have been reading on EVAL (I don't
> know which)
>
> Avoid using EVAL inside of a loop, rather EVAL the loop, as EVAL will
> REINTERPERET with EVERY ITERATION...so..using an EVAL inside of a loop
> causes a huge performance loss...and it is doubly so in a nested loop.
It is the difference between a 'q' or no 'q'.
eval q{'stuff'}; will parse and execute 'stuff' at run time; however,
eval {'stuff'}; is a form of exception handling. Here the block is parsed
at the same time as the surrounding code. I have not read any of the Wrox
books, so I won't comment on them; however, I would suggest if they do not
make the distinction between the two forms of eval you try another book.
Like, say, the Camel. For more information on eval: 'perldoc -f eval'
> Might I suggest using the DBI methods of retrieving ERROR codes REALLY
> is faster...AND: (Since this is postgres..PREPING and EXECUTING is
> useless...PG doesn't understand PREPARE the driver just allows it
> since it is a DBI call...DO is more practical here:
In what way? 'do' just calles prepare and execute for you (I did not
dig, but that is what DBI does by default, and I don't think Pg overrides
it), so you could say that it might be a tad slower because you have the
added hit of an additional method call.
===