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