Hi Thomas, Consider this issue solved for me - I wanted to make sure that for the longer term a bigger query cache was contemplated.
A related part of this issue is that Parser.prepareCommand(String); is costly for large SQL statements. In particular the Parser.initialize(String) method seems to be a CPU hog. When I say "large SQL statement" I mean > 3000 characters, and 150 parameters. I attempted to do some profiling to find if there were any simple optimisations available, but alas the Parser.initialize() method is complex and not easy to profile in more detail. Regards, Steve McLeod On Sunday, 2 August 2015 20:14:30 UTC+2, Thomas Mueller wrote: > > Hi, > > If you use PreparedStatement, and the re-use them, within the same > connection, you should be fine, even without the "query cache". > > The cache "query cache" is only for those cases where you use Statement > instead of PreparedStatement, or if you re-create the PreparedStatement > each time. That includes the case if you use a simple connection pool. > > Could explain what you do, maybe with a simple code example? > > Regards, > Thomas > > > On Sun, Aug 2, 2015 at 2:43 PM, Steve McLeod <steve....@gmail.com > <javascript:>> wrote: > >> Hi all, >> >> I was doing some application profiling today. I found that in our >> real-world scenario of loading a database using many different very long >> SQL statements, a significant amount of H2's CPU time was being spent in >> org.h2.command.Parser.initialise(). >> >> Further investigation revealed that by default, H2 caches only 8 prepared >> statements in a least recently used cache. Use 9 or more prepared >> statements repeatedly - as our application does - and you lose all benefit >> of the prepared statement cache. The effect is the same as if the query >> cache was set to 0, except for the continual overhead of cache churn. >> >> I performed my profiling again, this time with QUERY_CACHE_SIZE set to >> 100. This time I managed to run 40% more queries in the same timeframe, >> measured over several minutes of sustained inserts, updates, selects, and >> merges. >> >> I proposed that the value for QUERY_CACHE_SIZE should by default be >> significantly higher. >> >> >> >> -- >> 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...@googlegroups.com <javascript:>. >> To post to this group, send email to h2-da...@googlegroups.com >> <javascript:>. >> Visit this group at http://groups.google.com/group/h2-database. >> For more options, visit https://groups.google.com/d/optout. >> > > -- 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 http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
