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.
