Re: Performance problems???

James Patterson wrote:
>
> I have observed some disturbing behavior with the latest (7.1.3) version of
> PotgreSQL.
>
> In an application that I am working on with a team of folks, there is a
> specific need to execute a series of SQL statements similar to those used in
> the 'loaddb.pl' script included below. Without getting into better ways to
> increment rowid's (this code is part of another tool that we are using), I'd
> like to know why I get the following results with PostgreSQL and MySQL.
>
> In 3 separate runs I get the following PostgreSQL results:
>
> o 1 - 2000 records inserted in 12 seconds.
> o 2001 - 4000 records inserted in 16 seconds.
> o 4001 - 6000 records inserted in 20 seconds.
>
> You see, there is a clear performance degradation here that is associated
> with the number of records in the database. It appears that the main culprit
> is the update statement that is issued (see 'loaddb.pl' script below). This
> performance behavior is not expected. Especially with so few rows in such a
> small table.
>
> In 3 separate runs I get the following MySQL results:
>
> o 1 - 2000 records inserted in 6 seconds.
> o 2001 - 4000 records inserted in 5 seconds.
> o 4001 - 6000 records inserted in 6 seconds.
>
> You see, MySQL performs as expected. There is no performance degradation
> here that is related to the number of records in the database tables.
>
> I have been a huge fan and advocate of PostgreSQL. I was stunned to see this
> behavior. I am hoping that it is either a bug that has been fixed, or that I
> can alter my PostgreSQL configuration to eliminate this behavior.
>
> I have an urgent need to resolve this situation. If I cannot solve the
> problem soon, I will be forced to drop PostgreSQL in favor of MySQL. This is
> not something that I wish to do.

You really should us e a sequence.

You will most likely need to change the way you create sequence numbers
even for mysql
as the following is not safe on non-transactional DB.

> my $inc_id = $dbh->prepare("update control set next_id = next_id + 1");
> my $get_id = $dbh->prepare("select next_id from control");

if two backends happen to interleave their queries

1> my $inc_id = $dbh->prepare("update control set next_id = next_id +
1");
2> my $inc_id = $dbh->prepare("update control set next_id = next_id +
1");
1> my $get_id = $dbh->prepare("select next_id from control");
2> my $get_id = $dbh->prepare("select next_id from control");

then both will get the same next_id which is probably not what you want.

-------------
Hannu