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