Hi Val,
The reason for the execution time difference that you see between full scan
of the original table (LAZY_QUERY_EXECUTION, 20min) vs. scan of the
intermediate temp table (6 min), could be related to the fact that database
may be very sparsely populated (as a result of inserts/updates
You can turn LAZY_QUERY_EXECUTION on and off using
SET LAZY_QUERY_EXECUTION ON/OFF
But, as I said earlier, it has restrictions - it locks internal data
structures for the duration of retrieveing the resultset, so it is not
great for setups that need multiple concurrent queries.
--
You
If I understand what Evgenij is saying, that temp file is how the
h2-db-engine behaves, afterall, it is NOT the result set, but is a internal
temporary H2 structure.
I still dont understand why ResultSet iteration so much faster from this
temporary structure, compared to directly from the
Hello.
There is nothing to do with temporary results in H2. If you have large
queries and they may return parts of results early, use
`LAZY_QUERY_EXECUTION=TRUE`. If some query doesn't support lazy execution,
there is nothing to do with it in H2 itself. With
`LAZY_QUERY_EXECUTION=TRUE` H2
I haven't traced it through all the way.
*org.h2.result.LocalResultImpl#addRowsToDisk
*is a good starting point.
By the way I was using the word serialize 'loosely', its not using Java
serialization to serialize the result set. From a quick code review, its
placing the result sets into
Noel and Val, good morning.
Would you like to give us a hint, where exactly this
"temporally serialization of a resultset" happens in the code?
We could try to modify it so it writes Flat ASCII files or CSV files
instead of binary blobs (on demand, per hint), which would enable us to
read line by
> On Tuesday, November 10, 2020 at 12:03:30 AM UTC-5 val wrote:
> I'm guessing this is how the h2 client/driver (not the db engine it
self) behaves to handle a ResultSet. It will
> fully serialize the contents of the ResultSet to disk first and then
serve that. Perhaps this is
Well here I go into a monologue, answering my own question.
After searching for many fruitless hours, I finally stumbled on a useful
post, here, https://groups.google.com/g/h2-database/c/rgpD_y3Xp7w
Adding *LAZY_QUERY_EXECUTION=1 * to the connection url is closer to what I
was looking for.
On further inspection I can see that H2 is writing a large temp file,
before my results start flowing. 20+ Gigs of data in about 23 minutues.
Likely the whole contents of my large table rewritten to disk.
I'm guessing this is how the h2 client/driver (not the db engine it self)
behaves to
