Re: 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
>
> ----------------------------------------------------------------------
> ---
> "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")"