Re: SELECT very slow

PFC wrote on 15.06.2005 22:04:

>
>> It's not the program or Java. The same program takes about 20 seconds
>> with Firebird and the exactly same data.
>
>
> Hm, that's still very slow (it should do it in a couple seconds like
> my PC does... maybe the problem is common to postgres and firebird ?)
>
> Try eliminating disk IO by writing a set returning function which
> returns 1000000 rows, something simple like just a sequence number and
> a text value... if this is slow too... i don't know...

> do you have an antivirus or zonealarm or something ?
Wouldn't that affect all DB access not only PG? And as I said, all other

The 20 seconds are ok. This includes processing of the data in the
application. If I simply loop over the result set and get each column's
value without further processing it takes 4 seconds with Firebird.

Basically I'm doing the following:

rs = stmt.executeQuery("select * from foo");
while (rs.next())
{
for (int i=0; i < 4; i++)
{
Object o = rs.getObject(i+1);
}
}

As I said in my other post, the behaviour/performance in PG is dependent on
the autocommit setting for the connection.

With autocommit set to false the above code takes about 3 seconds in PG
but wit autocommit set to true, PG takes 3 minutes! It seems that it also
is very dependent on the fetchsize (apparently the number of rows that are
cached by the driver). Anything above 100 seems to slow down the overall
process.

Regards
Thomas