Re: Perl DBI, PostgreSQL performance question

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