database chunking

[yonestar <yo...@higtek.com>]

Hi,

I have a large table (1 million rows).  I want to write its contents to a
file, in a specified csv format.  

If i try to get all the rows at once, i run out of heap space (of course). 
i can break it into chunks, using Java's Query.setFirstResult() and
Query.setMaxResults().  but, this is slow. judging from some benchmarking i
did, it seems that all the records up to the first requested one are
retrieved and ignored.  (i.e. setFirstResult(n) will retrieve all the
records but simply toss the ones < n)  using this method is very slow over 1
million rows.

what i'm looking for is some way to "get the next 1000 rows", where the DB
remembers the last position in the table-- i.e., it doesn't have to seek to
the entry point each time.  how can i efficiently break the table into
chunks?

thanks!!
-- 
View this message in context: http://www.nabble.com/database-chunking-tp14339770p14339770.html
Sent from the Apache Derby Developers mailing list archive at Nabble.com.

Re: database chunking

[Kristian Waagan <Kr...@Sun.COM>]

Oystein Grovlen - Sun Norway wrote:
> yonestar wrote:
>> Hi,
>>
>> I have a large table (1 million rows).  I want to write its contents to a
>> file, in a specified csv format.  
> 
> Have you tried to use Derby's built-in stored procedure  for export, 
> SYSCS_UTIL.SYSCS_EXPORT_TABLE() ?
> 
>>
>> If i try to get all the rows at once, i run out of heap space (of 
>> course). i can break it into chunks, using Java's 
>> Query.setFirstResult() and
>> Query.setMaxResults().  but, this is slow. judging from some 
>> benchmarking i
>> did, it seems that all the records up to the first requested one are
>> retrieved and ignored.  (i.e. setFirstResult(n) will retrieve all the
>> records but simply toss the ones < n)  using this method is very slow 
>> over 1
>> million rows.
> 
> I do not understand why you should necessarily run out of heap space. 
> Derby does not need to store the entire table in memory in order to 
> select all the rows.

I agree.
What settings are you running with for the Derby page cache and the Java 
heap?
Do you have one or more indexes on your table?
Do you run out of heap space if you issue a "SELECT COUNT(*)" on your 
big table, or have you already run this before you start fetching the rows?

It would be interesting to see a histogram of the heap just before you 
run out of heap space.



regards,
-- 
Kristian

>>
>> what i'm looking for is some way to "get the next 1000 rows", where 
>> the DB
>> remembers the last position in the table-- i.e., it doesn't have to 
>> seek to
>> the entry point each time.  how can i efficiently break the table into
>> chunks?
>>
>> thanks!!
> 
> 

Re: database chunking

[Oystein Grovlen - Sun Norway <Oy...@Sun.COM>]

yonestar wrote:
> Hi,
> 
> I have a large table (1 million rows).  I want to write its contents to a
> file, in a specified csv format.  

Have you tried to use Derby's built-in stored procedure  for export, 
SYSCS_UTIL.SYSCS_EXPORT_TABLE() ?

> 
> If i try to get all the rows at once, i run out of heap space (of course). 
> i can break it into chunks, using Java's Query.setFirstResult() and
> Query.setMaxResults().  but, this is slow. judging from some benchmarking i
> did, it seems that all the records up to the first requested one are
> retrieved and ignored.  (i.e. setFirstResult(n) will retrieve all the
> records but simply toss the ones < n)  using this method is very slow over 1
> million rows.

I do not understand why you should necessarily run out of heap space. 
Derby does not need to store the entire table in memory in order to 
select all the rows.
> 
> what i'm looking for is some way to "get the next 1000 rows", where the DB
> remembers the last position in the table-- i.e., it doesn't have to seek to
> the entry point each time.  how can i efficiently break the table into
> chunks?
> 
> thanks!!


-- 
Øystein

Re: database chunking

[yonestar <yo...@higtek.com>]

Upon further investigation, I'm less sure about how setFirstResult() and
setMaxResults() are actually working. does anyone know?

using those methods, getting successive chunks and starting from the first
row, i saw times of 20 secs, 50 s, 50 s, 50 s.  leads me to think there is a
constant time penalty for seeking to not the first row.
-- 
View this message in context: http://www.nabble.com/database-chunking-tp14339770p14339792.html
Sent from the Apache Derby Developers mailing list archive at Nabble.com.