tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Martin Jacobson <marti...@libero.it>
Subject Re: DB Connection
Date Wed, 28 May 2003 14:36:34 GMT
Munteanu Gabriel wrote:
> Guys, this is very interesting discussion.
> I have read all the posts.
> I still have another problem.
> What about VERY large Result Sets?
> I have a portal with 1 000 000 records[books related].
> When the user searches for romance, there are more than 200 000 records
> returned.
> I can't use CachedResultSet [I would have to hold all this in memory, in an
> object registerd in the HTTPSession]
> also I can't use the ArrayList.
> I am also using a DB pool system and I want to return the connection.
> Is there a solution? [better said an optimal solution].
> If the ResultSet doesn't hold all the records in memory[RAM],
> (setFetchSize(int) -> sets how many rows to be brought into memory), where
> is the rest? A concrete case:
> "select name from books" -> returns 850 000 records.
> statement->setFecthSize(25);
> the rest of 849975 records are left in the database?, on the hard-disk? or
> the database is making all this resultset
> internally, and itself uses RAM and some swap? Does anyone knows all this
> internal action, how it is going on?
> 
> Comments, URLs appreciated.
> Info: I use Postgresql if it counts.
> In the case that I will have to not return the connection to the pool, and,
> because ResultSet doesn't hold in memory all the records I would save it int
> the HTTPSession, HOW AM I going to know when to release it and return the
> connection to the pool?
> 

In this case, I would query the database initially, asking ONLY for the 
unique id of each row (or the master row if there's a join involved). I 
would then cache the entire result set, and then fetch the complete 
records subsequently, by doing something like:

SELECT whatever FROM mytable WHERE oid IN (oid1, oid2, oid3, ...);

Of course, you'd have to handle the case where a cached oid is deleted 
between the initial query, and the later one.

If the unique id (oid in my example above) is an integer, you can cache 
the entire database in 4Mb.

HTH
Martin


---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org


Mime
View raw message