If you limit statement caching to PreparedStatement the best place for
the cache would be the StatementManager.
The StatementManager compile() method checks if the SQL and its
Statement object already exists. The Statement is removed when it is
closed. All Statements are cleared when the connection and the session
are closed. So for your usage, you can put a non-static ConcurrentMap in
org.hsqldb.Database and each time a statement is compiled, you add to
the ConcurrentMap and each time the lookup in compile() does not find
the SQL and its statement, you try retrieving from the Concurrent map
and let the compile() method perform its other checks. Note there is a
different map for each schema (same SQL compiled with a different
current schema can target different tables). Therefore you need a map
of schema name to ConcurrentMap to be precise. This method doesn't need
changes to resetSession and works with or without a connection pool.
Fred
On Fri, May 27, 2016, at 09:49, Daniel Walsh wrote:
> 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...@users.sourceforge.net> 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[1]
>>> _________________________________________________
>>> hsqldb-developers mailing list
>>> hsqldb-developers@lists.sourceforge.net
>>> 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 hsqldb-
>> develop...@lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
>>
> ----------------------------------------------------------------------
> --------
> What NetFlow Analyzer can do for you? Monitors network bandwidth
> and traffic
> patterns at an interface-level. Reveals which users, apps, and
> protocols are
> consuming the most bandwidth. Provides multi-vendor support for
> NetFlow,
> J-Flow, sFlow and other flows. Make informed decisions using capacity
> planning reports.
> https://ad.doubleclick.net/ddm/clk/305295220;132659582;e[2]
> _________________________________________________
> hsqldb-developers mailing list
> hsqldb-developers@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
Links:
1. https://ad.doubleclick.net/ddm/clk/304595813%3B131938128%3Bj
2. https://ad.doubleclick.net/ddm/clk/305295220%3B132659582%3Be
------------------------------------------------------------------------------
What NetFlow Analyzer can do for you? Monitors network bandwidth and traffic
patterns at an interface-level. Reveals which users, apps, and protocols are
consuming the most bandwidth. Provides multi-vendor support for NetFlow,
J-Flow, sFlow and other flows. Make informed decisions using capacity
planning reports. https://ad.doubleclick.net/ddm/clk/305295220;132659582;e
_______________________________________________
hsqldb-developers mailing list
hsqldb-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
