Skip site navigation (1) Skip section navigation (2)

Re: JDBC and processing large numbers of rows

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: David Wall <d(dot)wall(at)computer(dot)org>
Cc: "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: JDBC and processing large numbers of rows
Date: 2004-05-11 19:56:34
Message-ID: 1084305394.1534.121.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-jdbc
David,
Use cursors to page through really large result sets

Dave

On Tue, 2004-05-11 at 11:37, David Wall wrote:
> This is more towards JDBC than PG in particular, but since we support PG,
> Oracle and eventually other DBMS via JDBC, I thought I'd see if anybody has
> any good ideas on how do the following in a somewhat portable way via JDBC
> (by large, I'm talking about thousands to millions of rows -- significant
> amounts of data)
> 
> 1) Often people need to "page through"  large result set.  Some DBs support
> the LIMIT,OFFSET construct of SELECT, and while not super efficient, at
> least it only returns the window of the result set that is of interest.  How
> do most people handle such paging through of data?  I've heard people cache
> the entire result set, but that's not practical because of the memory
> overhead and, with the web, not even knowing when to get rid of the
> resultset.  Do people use work tables or other schemes to "pre-paginate" the
> results but store it in a temporary table?
> 
> 
> 2) How do people process many records in a large resultset?  For example,
> scanning through a list of rows and then doing some processing based on
> those rows, and then perhaps updating the rows to indicate that they've been
> processed.
> 
> 
> 3) How do people use cursors in JDBC?  Being able to FETCH seems like a nice
> way to handle question #2 above in a batch program, since only a subset of
> rows needs to be retrieved from the db at a time.  Cursors probably don't
> work for question #1 above since keeping a transaction alive across page
> views is generally frowned upon and even hard to accomplish since it means
> locking up a connection to the db for each paging user.
> 
> 
> Any pointers would be appreciated.
> 
> Thanks,
> David
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
> 
> 
> 
> !DSPAM:40a0f4e2176411409110076!
> 
> 
-- 
Dave Cramer
519 939 0336
ICQ # 14675561


In response to

Responses

pgsql-jdbc by date

Next:From: David WallDate: 2004-05-11 20:32:29
Subject: Re: JDBC and processing large numbers of rows
Previous:From: Andy ZeneskiDate: 2004-05-11 16:57:43
Subject: Re: Result Set Cursor Patch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group