Re: Why is JDBC so slow?

On Tue, Sep 02, 2003 at 02:05:15PM -0400, Joseph Shraibman wrote:
> For this test I created a table named tt with ten entries in it. These
> selects thus select 10^6 rows. As you can see time for jdbc is much
> slower than psql. Why is this?

> ]$ time java -Xmx256m JDBCclient -U postgres -h localhost -c "select
> 'blah' from tt tta, tt ttb, tt ttc, tt ttd, tt tte, tt ttf;" playpen >
> /dev/null
>
> real 0m27.271s
> user 0m21.040s
> sys 0m0.710s

With those heap settings you spend a lot of time doing GC. As pointed out by
others you can reduce the memory footprint of the driver via setFetchSize().
A quick estimate from the GC output shows that about 50% of allocations
immediately become garbage, which can probably be improved on if it's coming
from the driver. That's not an unusual allocation pattern for java programs,
though.

With -Xmx256m and --nooutput --time:

query time: 25.696 seconds

real 0m26.455s
user 0m21.050s
sys 0m0.450s

(GC totals ~14 seconds)

With -Xmx256m -Xms256m and --nooutput --time:

query time: 16.739 seconds

real 0m17.494s
user 0m12.400s
sys 0m0.670s

(GC totals ~5 seconds)

This can obviously still be improved on, but I think some of the blame for
slow execution can be pinned on your heap settings not being tuned for the
object allocation the client does.

Incidentally, your use of getFetchSize() to size the return array in
getResults() seems buggy .. I believe the "correct" way is to do
ResultSet.last() then ResultSet.getRow(). But that'll probably force the
whole resultset into memory, too ..

-O