experience sharing: select query returns more records than necessary

To share with you:

I am using a select statement to try to retrieve the first 50 records found
in a large table by reading the result set one by one (up to 50). However,
the query execution (the call to executeQuery in JDBC) takes quite a lot of
time, eg, 10 seconds and quite a lot of records were returned as shown in
the network packets captured.

In contrast, if I issue the query on the DB server in the psql console, it
returns records almost immediately. Finally I found that it is because the
postgreSQL JDBC driver is pre-fetching a lot (all?) of the records. To fix
the problem, one can call setFetchSize(50) on the statement.

-----
--
Kent Tong
Wicket tutorials freely available at http://www.agileskills2.org/EWDW
Axis2 tutorials freely available at http://www.agileskills2.org/DWSAA
--
View this message in context: http://www.nabble.com/experience-sharing%3A-select-query-returns-more-records-than-necessary-tp21556532p21556532.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.