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?
>
Because this is not a fair comparison. The 'psql' program knows in advance what
to do with the data, while the JDBC driver will allow you do do anything you
want with it and can guess what it is.
So the psql program reads data and spits it out (to /dev/null, which is fast)
every row it reads.
The JDBC driver has to store all these rows in memory and wait for you to tell
it what you want to do with it.
To prevent data to be stored, use setFetchSize() (in 7.4 drivers) or just use a
cursor.
Regards,
Fernando
> The code for JDBCclient is at: http://www.tupari.net/JDBCclient.java
>
> Tests were run on pg 7.4b2 on a redhat 9 machine with java 1.4.2_01
>
> ]$ time psql -c "select 'blah' from tt tta, tt ttb, tt ttc, tt ttd, tt
> tte, tt ttf;" playpen > /dev/null
>
> real 0m4.321s
> user 0m1.390s
> sys 0m0.150s
> ]$ 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
> ]$ time psql -c "select 1 from tt tta, tt ttb, tt ttc, tt ttd, tt tte,
> tt ttf;" playpen > /dev/null
>
> real 0m5.125s
> user 0m1.660s
> sys 0m0.030s
> ]$ time java -Xmx256m JDBCclient -U postgres -h localhost -c "select 1
> from tt tta, tt ttb, tt ttc, tt ttd, tt tte, tt ttf;" playpen > /dev/null
>
> real 0m26.462s
> user 0m20.180s
> sys 0m0.970s
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
--
Fernando Nasser
Red Hat - Toronto E-Mail: fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario M4P 2C9