From: | "Wm(dot)A(dot)Stafford" <stafford(at)marine(dot)rutgers(dot)edu> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | When limit is greater than 813,500 activity seems to slow way down or stop |
Date: | 2008-10-03 21:18:51 |
Message-ID: | 48E68C3B.3020205@marine.rutgers.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
I'm writing a Java client to load Google Base with records from our
PostgreSQL 8.3 db. The table I'm querying has about 15 million records
and I was hoping to just loop over this table creating one Google entry
for each db row. However, using the code below, if the limit is greater
than around 813,500 the application just sits there apparently doing
nothing. For lower values it starts immediately and runs to
completion. I'm guessing that this might be some sort of memory problem
but I thought using a cursor-based, read-only result set would prevent
memory issues.
Any ideas why 813,500+ seems to be a magic number?
Thanks for any help or advice,
-=beeky
public static final String dataQuery = "select
A.cache_id,A.validname,A.validnameauthor, A.daycollected,
A.monthcollected, A.yearcollected, A.latitude, A.longitude, A.depth,
B.resource_full_name,
'http://marinespecies.org/aphia.php?p=taxdetails&id=' || C.worms_id as
worms_url, 'http://data.iobis.org/distRec.do?id='|| A.cache_id as
dist_rec_url from obis.cache A, obis.obis_resources B, obis.taxa C where
A.resource_id=B.resource_id and A.taxon_id=C.taxon_id offset 0 limit
800000" ;
conn.setAutoCommit(false);
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY) ;
stmt.setFetchSize(600);
rs = stmt.executeQuery(dataQuery) ;
while (rs.next()) {
... process next row
From | Date | Subject | |
---|---|---|---|
Next Message | Gerald Rosenberg | 2008-10-05 17:14:29 | Problem with Hibernate/Spring/Postgres configuraiton |
Previous Message | Kris Jurka | 2008-10-02 17:48:44 | Re: Malformed URLs |