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 database using LAZY_QUERY_EXECUTION. We are talking 6 minutes vs 20 minutes. I can understand if there were other requirements like sorting, filtering or joining; then I could see how that would require a temporary structure. But in this case the query is a basic SELECT * FROM TABLE, why would the LAZY_QUERY_EXECUTION approach not be able to iterate all the rows in the same 6 minutes as with the temporary structure. Perhaps the rows in the database are stored differently in the db than in the temporary structure that makes them easier/cheaper to iterate? Separately; should we use LAZY_QUERY_EXECUTION=TRUE by default? If it can already detect if it needs to use a temporary structure or not; is there any drawback? Its a bit of an odd programming pattern to have to setup different connection urls depending on what you are trying to select. On Tuesday, November 10, 2020 at 8:13:24 PM UTC-5 Evgenij Ryazanov wrote: > 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 uses LocalResult if and only if rows need a > post-processing, such as sorting, distinct filtration, or OFFSET / FETCH > filtration and these operations can't be performed early, for example, > because there are no compatible indexes or some previous step needs a > complete set of rows. In all these cases rows passed to LocalResult are not > the same rows as will be returned by ResultSet. > -- 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 h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/fc7ecce5-c0fe-4b42-a59e-b65c878b0edan%40googlegroups.com.
