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!!
