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 <steve....@gmail.com
> <javascript:>> 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 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.
