You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Zithulele Basil Shange <zi...@cibecs.com> on 2006/03/01 09:31:11 UTC
RE: Large Resultset
I am using the TableModel to buffer the results in memory for the Table or
passing it to Jasper report. The memory is consumed by the Report generation
as well as TableModel for rows about 10000 upwards.
Is there a way of traversing a resultset or DB on a per set of rows? If I
can keep the ResultSet and run through it on paging by the size that I want.
What is the cost of the ResultSet in memory?
-----Original Message-----
From: Stanley Bradbury [mailto:Stan.Bradbury@gmail.com]
Sent: 28 February 2006 10:07 PM
To: Derby Discussion
Subject: Re: Large Resultset
Zithulele Basil Shange wrote:
> I have a DB (Derby) having 100's thousands of rows. I would like to
> select row using a condition and be able to page them. The problem is
> that if I page them in memory I get an outofmemory error or the rowset
> returns a couple of thousands instead of all the rows matching the
> condition.
>
> Is there a way of retrieving a large amount of data rows and be able
> to page them as well?
>
> I have tried to limitting the results by setMaxRows but paging them is
> a problem.
>
> Regards
> Zithulele
Hi -
I've not implemented a data paging system like yours but the answer to your
question is yes, Derby will allow you to retrieve large amounts of data in
a ResultSet. Derby should handle the buffering of data between the database
and the application without exhausting memory. If the outofmemory error
happens in derby classes then you will need to increase the maxHeap
allocated to Java.
As for paging the data this needs to be handled by the application and my
guess is this is where your problem lies. How are you
constructing/buffering your pages before displaying them? Is the
outofmemory error happening in these classes?
RE: Large Resultset
Posted by Zithulele Basil Shange <zi...@cibecs.com>.
Thanx. I will try the scrollable resultset and see if it will have any
difference. I can control the connection and destroy as soon as I don't want
it.
-----Original Message-----
From: Michael Segel [mailto:msegel@segel.com] On Behalf Of derby@segel.com
Sent: 01 March 2006 03:28 PM
To: 'Derby Discussion'
Subject: RE: Large Resultset
Uhm...
Then that's where your memory is being eaten up. Meaning that its not Derby
that is causing your memory issue.
The problem is that it sounds like you want to have a persistent result set.
(There was a discussion on c.d.i about server side cursors that is probably
similar to this....)
You have two solutions....
1) A scrollable cursor.
2) Use temp tables.
Note. Use #2 if you can not maintain control of your connection/cursors.
(re: n tier app, using connection pooling.)
HTH,
-Gumby
> -----Original Message-----
> From: Zithulele Basil Shange [mailto:zithuleles@cibecs.com]
> Sent: Wednesday, March 01, 2006 2:31 AM
> To: 'Derby Discussion'
> Subject: RE: Large Resultset
>
> I am using the TableModel to buffer the results in memory for the
> Table or passing it to Jasper report. The memory is consumed by the
> Report generation as well as TableModel for rows about 10000 upwards.
>
> Is there a way of traversing a resultset or DB on a per set of rows?
> If I can keep the ResultSet and run through it on paging by the size
> that I want.
> What is the cost of the ResultSet in memory?
>
> -----Original Message-----
> From: Stanley Bradbury [mailto:Stan.Bradbury@gmail.com]
> Sent: 28 February 2006 10:07 PM
> To: Derby Discussion
> Subject: Re: Large Resultset
>
> Zithulele Basil Shange wrote:
>
> > I have a DB (Derby) having 100's thousands of rows. I would like to
> > select row using a condition and be able to page them. The problem
> > is that if I page them in memory I get an outofmemory error or the
> > rowset returns a couple of thousands instead of all the rows
> > matching the condition.
> >
> > Is there a way of retrieving a large amount of data rows and be able
> > to page them as well?
> >
> > I have tried to limitting the results by setMaxRows but paging them
> > is a problem.
> >
> > Regards
> > Zithulele
>
> Hi -
> I've not implemented a data paging system like yours but the answer to
> your question is yes, Derby will allow you to retrieve large amounts
> of data in a ResultSet. Derby should handle the buffering of data
> between the database and the application without exhausting memory. If
> the outofmemory error happens in derby classes then you will need to
> increase the maxHeap allocated to Java.
>
> As for paging the data this needs to be handled by the application and
> my guess is this is where your problem lies. How are you
> constructing/buffering your pages before displaying them? Is the
> outofmemory error happening in these classes?
>
RE: Large Resultset
Posted by de...@segel.com.
Uhm...
Then that's where your memory is being eaten up. Meaning that its not Derby
that is causing your memory issue.
The problem is that it sounds like you want to have a persistent result set.
(There was a discussion on c.d.i about server side cursors that is probably
similar to this....)
You have two solutions....
1) A scrollable cursor.
2) Use temp tables.
Note. Use #2 if you can not maintain control of your connection/cursors.
(re: n tier app, using connection pooling.)
HTH,
-Gumby
> -----Original Message-----
> From: Zithulele Basil Shange [mailto:zithuleles@cibecs.com]
> Sent: Wednesday, March 01, 2006 2:31 AM
> To: 'Derby Discussion'
> Subject: RE: Large Resultset
>
> I am using the TableModel to buffer the results in memory for the Table or
> passing it to Jasper report. The memory is consumed by the Report
> generation
> as well as TableModel for rows about 10000 upwards.
>
> Is there a way of traversing a resultset or DB on a per set of rows? If I
> can keep the ResultSet and run through it on paging by the size that I
> want.
> What is the cost of the ResultSet in memory?
>
> -----Original Message-----
> From: Stanley Bradbury [mailto:Stan.Bradbury@gmail.com]
> Sent: 28 February 2006 10:07 PM
> To: Derby Discussion
> Subject: Re: Large Resultset
>
> Zithulele Basil Shange wrote:
>
> > I have a DB (Derby) having 100's thousands of rows. I would like to
> > select row using a condition and be able to page them. The problem is
> > that if I page them in memory I get an outofmemory error or the rowset
> > returns a couple of thousands instead of all the rows matching the
> > condition.
> >
> > Is there a way of retrieving a large amount of data rows and be able
> > to page them as well?
> >
> > I have tried to limitting the results by setMaxRows but paging them is
> > a problem.
> >
> > Regards
> > Zithulele
>
> Hi -
> I've not implemented a data paging system like yours but the answer to
> your
> question is yes, Derby will allow you to retrieve large amounts of data
> in
> a ResultSet. Derby should handle the buffering of data between the
> database
> and the application without exhausting memory. If the outofmemory error
> happens in derby classes then you will need to increase the maxHeap
> allocated to Java.
>
> As for paging the data this needs to be handled by the application and my
> guess is this is where your problem lies. How are you
> constructing/buffering your pages before displaying them? Is the
> outofmemory error happening in these classes?
>