I have a very simple table:
CREATE TABLE myTable(binA BINARY(34) PRIMARY KEY, binB BINARY(21), value
BIGINT);
With an index (which should be unrelated to this question)
CREATE INDEX myTableIndex ON myTable(binB);
It contains > 8 million records (binA and binB contain basically random
bytes, but again this is unrelated to my question).
On startup I wish to read through it all to generate a graph, so basically:
read next element, do something, rinse and repeat
My problem is that it takes about 10 minutes before i get the first result,
once I get the first result everything is snappy.
Here is my statement: SELECT binA, binB FROM myTable
and my Java code looks like this:
ResultSet result = psLoad.executeQuery();
while(result.next()) {
byte[] binA = result.getBytes(1);
byte[] binB = result.getBytes(2);
... do something ...
}
My guess is that everything is read into memory in the first line. Is there
any way to only read one record at a time or somehow speed it up?
I have tried "SET MAX_MEMORY_ROWS = 10000000", but that didn't seem to have
any effect.
I have tried to do multiple select statements where I LIMIT the result set
combined with OFFSET, but apparently the offset takes linear time in the
number of rows to skip.
I have another DB with about 400,000 records where the first results come
within one or a few seconds.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
