I have implemented this for my special case and I keep having problems: I use a prepared select statement with an LIMIT ? OFFSET ? clause I retrieve just 100000 of the 100 million rows every time. I initially still got out of memory exceptions and ended up giving 80G heap space to the process.
This works well initially: when I observe the process in jconsole, each select statement only takes a few minutes to get processed and uses less than 20G. However the more iterations of carrying out the prepared select with new offset limit values are done, the more memory is consumed and even worse, much worse, the more time it takes. The most recent batch of processing 100000 rows took all night! (BTW "processing" means just reading the rows from the result set using next(), and writing them to a file so the slow-down cannot come from my own code). Is anything known about this kind of behaviour or could I still be doing something that could get improved and solve my problems? My feeling is that even though offset and limit is used, the database spends a large amount of time to actually get to the starting offset of the result set, so the time required to do this grows with increasing offset. So using this limit/offset approach to get around the problem of huge result sets because there is no server side cursor support does not seem to be a usable solution to my problem either -- I guess I have to look for a different database. Any suggestions for a good replacement just for this specific task which an be used in embedded mode (without complex installation, setting up, etc.)? On Saturday, 5 November 2016 18:06:35 UTC, Johann Petrak wrote: > > Thanks for pointing this out -- I missed the corresponding remark in > http://www.h2database.com/html/advanced.html > I had only been looking at the documentation of setFetchSize here: > http://www.h2database.com/javadoc/org/h2/jdbc/JdbcStatement.html > > For me this is rather bad news because while this is one concrete example, > the software is written to handle arbitrary select statements (which could > eg already contain limit/offset clauses, but with rather large limit > numbers). > > To work around this with limit/offset also has the problem that there may > still > be a big overhead of creating too many such queries, depending on the > complexity > of the query while creating too few may run in the out of memory > problem again. > > On Saturday, 5 November 2016 17:48:44 UTC, Noel Grandin wrote: >> >> we don't do server-side cursors yet. which is what you're asking for. >> >> use SELECT..LIMIT..OFFSET >> >> to limit the amount of data you pull in one SELECT >> >> > -- 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 post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
