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 <steve.mcl...@gmail.com> 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 <steve....@gmail.com> 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.
>>> To post to this group, send email to h2-da...@googlegroups.com.
>>> 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.
>
--
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.
