newbie question: processing large database in chunks?  
Index ‹ java-programmer
Author Message
cerios
PostPosted: 2006-1-24 14:09:00 Top
java-programmer, newbie question: processing large database in chunks?
Hi,

When I
select * from XX
I'm geting an OutOfMemoryError. I'm using JDBC with Mysql,
and calls like:

ResultSet rs = dbc.executeQuery("select * from thetable");

My beginner's question: what's the right way to read and process the
table in chunks, to avoid running out of memory?

Is using LIMIT n1,n2 inside the query the right thing to do, giving
e.g. LIMIT 1,100 followed by another query using LIMIT 101,200, etc?
Is there a more efficient way?

I guess I'm surprised that it's even necessary. I would have thought
that the data would stay on the server until pulled to the client
by resultset.next() and a resultset.getXX(). But evidently
the whole table is being sent to the client??
 
Boris Stumm
PostPosted: 2006-1-24 16:47:00 Top
java-programmer >> newbie question: processing large database in chunks? cerios wrote:
> My beginner's question: what's the right way to read and process the
> table in chunks, to avoid running out of memory?

What do you want to do exactly?

> I guess I'm surprised that it's even necessary. I would have thought
> that the data would stay on the server until pulled to the client
> by resultset.next() and a resultset.getXX(). But evidently
> the whole table is being sent to the client??

Depends probably on the JDBC driver.

 
Mladen Adamovic
PostPosted: 2006-1-25 0:37:00 Top
java-programmer >> newbie question: processing large database in chunks? cerios wrote:
> When I
> select * from XX
> I'm geting an OutOfMemoryError. I'm using JDBC with Mysql,
> and calls like:
> ResultSet rs = dbc.executeQuery("select * from thetable");

Try to add JVM parametar -Xmx1000m
It suggest JVM to use 1GB heap for data.
Initial heap is to small (8 MB)
If your table is more than 1GB large you definitly shouldn't use select
* statement without where clause due to performance problem.


--
Mladen Adamovic
http://home.blic.net/adamm
 
 
Alun Harford
PostPosted: 2006-2-4 6:31:00 Top
java-programmer >> newbie question: processing large database in chunks? "cerios" <email***@***.com> wrote in message
news:230120062209110097%email***@***.com...
>
> Hi,
>
> When I
> select * from XX
> I'm geting an OutOfMemoryError. I'm using JDBC with Mysql,
> and calls like:
>
> ResultSet rs = dbc.executeQuery("select * from thetable");
>
Have a look at java.sql.Statement.setFetchSize(int size)

Alun Harford


 
 
 
Index ‹ java-programmer