Hi Thomas, Thanks for the tip. I spent time today trialling various connection pools with H2. Surprisingly, nothing I found compares with using H2's own query cache + connection pool for both speed and ease.
On Thursday, 6 August 2015 20:01:47 UTC+2, Thomas Mueller wrote: > > Hi, > > Did you try using a more advanced connection pool? One that re-uses > prepared statements. The H2 one is really simple and prevents that. > > Regards, > Thomas > > > > On Thursday, August 6, 2015, Steve McLeod <[email protected]> wrote: > >> Noel, I think you are right. I use this pattern for each query: >> >> public void insertARow(int x) { >> String sql = "insert into yada yada yada"; >> try (Connection conn = getConnectionFromConnectionPool(); >> PreparedStatement statement = conn.prepareStatement(sql)) { >> statement.setInt(1, x); >> statement.executeUpdate(); >> } >> } >> >> It is based on keeping the database as unlocked as possible, in my >> multi-threaded app. I may need to change the pattern a bit. >> >> I deduced that Parser.initialize, for an SQL statement with n characters >> * creates an array of n+1 ints >> * an array of n+1 chars >> * calls String.getChars() , which in turn calls System.arraycopy() for >> n+1 characters >> * calls new String() , which in turn calls System.arraycopy() for n+1 >> characters >> >> All of these result in memory that escapes the method, so will be created >> on the JVM's heap. >> >> Although this should all be blindingly fast, the fact that the rest of H2 >> is so fast, like you said, makes this show up. I think I was seeing this, >> because for an SQL statement with 3000 characters, being performed 10,000 >> times a second, roughly 3000 * 10 bytes * 10,000 invocations = 300 MB being >> allocated on the heap. And indeed, in my profiling, I noticed a lot of >> churn on the heap. >> >> Cheers, >> >> Steve >> >> >> On Wednesday, 5 August 2015 20:10:38 UTC+2, Noel Grandin wrote: >>> >>> The thing is, I don't think there is a problem. I think that your code >>> is not caching PreparedStatement 's properly, and the rest of H2 is so >>> fast, that the only thing left in the profile is the parser initialisation >>> :) >>> >>> On Wed, 05 Aug 2015 at 16:27, Steve McLeod <[email protected]> wrote: >>> >>>> Hi Noel, >>>> >>>> I've actually solved this problem of PreparedStatement caching for my >>>> app by increasing QUERY_CACHE_SIZE to 100. But I'm interested in helping >>>> solve the bigger issue of why it seems to take a comparatively significant >>>> time to create a PreparedStatement. >>>> >>>> Cheers, >>>> >>>> Steve >>>> >>>> >>>> On Wednesday, 5 August 2015 16:04:46 UTC+2, Noel Grandin wrote: >>>>> >>>>> >>>>> Thanks, I'll have a look tomorrow at them in detail. >>>>> >>>>> Tell me, how often is JdbcConnection@preparedStatement called compared >>>>> to how many times you execute a query? >>>>> >>>>> If it's every time, it means that your PreparedStatement caching is >>>>> not working, which would indicate a problem with >>>>> your connection pool, or something similar. >>>>> >>>>> -- >>>> 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/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 [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/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 [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/d/optout.
