Perl DBI, PostgreSQL performance question

From: Benjamin Franks <benjamin(at)dzhan(dot)com>
To: <dbi-users(at)perl(dot)org>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Perl DBI, PostgreSQL performance question
Date: 2001-12-14 15:35:06
Message-ID: 20011214073150.N30736-100000@crimea.dzhan.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2001-12-14 15:48:16 Re: initdb, separate fileystem?
Previous Message Thomas Lockhart 2001-12-14 15:07:34 Re: SQL QUERIES