Re: [Hsqldb-developers] Global Statement Compilation in HSQLDB

[Daniel W. <wal...@tc...>]

I'll investigate using a Connection Pool , i'm currently using the apache
BasicDataSource for connection retrieval.

However if every time I retrieve from the pool if resetSession is called
doesn't that clear the statement manager as you indicate, wiping out any
performance gains of persisting compiled PreparedStatements between
separate usages of the connection? Essentially I have many concurrent users
hitting the same statement structures with different values for ? , ? , ?
parameters. The statements are programatically generated, and quite finite,
between 100-5000 thousand possible statements depending on the scale of the
configuration.

For this reason at the level of the class ParserDQL I added a very basic
hack ( since this class is called in a stateful way , where fields are set
on it - so i was a bit careful ) , I added a private static final
ConcurrentMap of SQL String -> StatementQuery , on the basis that if the
sql strings are identical their compiled queries must also be identical, I
couldnt think of a scenario where they would need to be destroyed and
recreated , if the DDL changes then the statement would also need to change
by necessity, therefore the value in the cache would never get used again
.... so except for the glaring memory leak at this moment it would work in
principle.

So when compileCursorSpecification is called on this class , from digging
through the code I knew the sqlString was defined at this time, so i do a
simple map lookup and return if found, or compile , push to map , return if
not.
I identified the StatementQuery as the only costly part, so I cache that
state-less element and cannot really see any drawbacks like you mentioned
regarding closing sessions etc.

Thanks for discussing this matter in great depth btw , I'm a large fan of
HSQLDB and have used it in projects @ my company for several years.

Daniel



On 26 May 2016 at 20:48, Fred Toussi <fr...@us...> wrote:

> There was a global Prepared Statement cache in version 1.8. This was too
> complex as it had to keep track of closed sessions and statements closing
> on some sessions and not on others. The cache was moved to individual
> sessions in version 2.0.
>
> You can use a connection pool. There are two options. The
> org.hsqldb.jdbc.JDBCPool or a generic JDBC pool.
>
> The org.hsqldb.Session class has a resetSession() method that calls
> statementManager.reset(). The JDBCPool calls the resetSession() method.
> Therefore you need to modify either class to not reset the
> StatementManager. Third party pools may or may not be resetting the HSQLDB
> session upon reuse, so you need to check.
>
> Fred Toussi
>
>
>
> On Thu, May 26, 2016, at 19:41, Daniel Walsh wrote:
>
> Hi Devs,
>
> I have a question I couldn't find an answer for online or by investigating
> the HSQLDB code myself.
>
> Is there a reason currently in the implementation that the Stateless
> aspect of a Prepared Statement when compiled isn't stored in a global
> non-connection level cache ?
>
> I have a use case where connections are created and destroyed regularly
> and each connection calls 100+ prepared statement creation and executions
> during its lifecycle, in a test case that encompasses many other factors I
> increased performances by creating a naive low level cache of the compiled
> statement to be used each time a new PreparedStatement with a matching sql
> string was created.
>
> This had a massive benefit to performance because the statements being
> compiled are quite complex.
>
> Is this a feature that hasn't been implemented on purpose ? Or just an
> open gap ?
>
> Thanks,
> Daniel Walsh
> Software Dev
>
> ------------------------------------------------------------------------------
> Mobile security can be enabling, not merely restricting. Employees who
> bring their own devices (BYOD) to work are irked by the imposition of MDM
> restrictions. Mobile Device Manager Plus allows you to control only the
> apps on BYO-devices by containerizing them, leaving personal data
> untouched!
> https://ad.doubleclick.net/ddm/clk/304595813;131938128;j
> *_______________________________________________*
> hsqldb-developers mailing list
> hsq...@li...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
>
>
>
> ------------------------------------------------------------------------------
> Mobile security can be enabling, not merely restricting. Employees who
> bring their own devices (BYOD) to work are irked by the imposition of MDM
> restrictions. Mobile Device Manager Plus allows you to control only the
> apps on BYO-devices by containerizing them, leaving personal data
> untouched!
> https://ad.doubleclick.net/ddm/clk/304595813;131938128;j
> _______________________________________________
> hsqldb-developers mailing list
> hsq...@li...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
>
>