In recent versions of HSQLDB, Statement objects are not generally
mutable and can be shared concurrently. The only mutable aspect is the
setCursorPropertiesRequest() method that is checked for in
StatementManager.compile(). Statements that are out-of-date are also
rejected by the same method.
Fred
On Fri, May 27, 2016, at 11:26, Daniel Walsh wrote:
> I initially added the Statement Cache to the Statement Manager class,
> but I realized that sharing a Statement object concurrently between
> many connections would mean mixing state between concurrent users
> causing functional errors. To do that correctly I would need to
> implement a global cache of statements that never get "used" , but
> rather on retrieval get cloned , so their state isn't shared, and then
> that Statement follows a regular lifecycle, being returned to the
> connection, pooled as normally done, closed and destroyed as usual.
> But the object in the cache would be retained, un-modified after
> creation , used as a template so-to-speak.
>
> And that whole solution as described above seemed to be less
> performant than just caching the non-modifiable aspect of the
> Statement objects and let them be constructed natively as is
> currently done.
>
> Am I correct in thinking that sharing statement objects across
> connections would cause data inconsistencies? It appears that way.
>
> Daniel
>
>
> On 27 May 2016 at 11:07, Fred Toussi
> <fr...@users.sourceforge.net> wrote:
>> 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
>>
>> --------------------------------------------------------------------
>> ----------
>> 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-
>> 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[3]
> _________________________________________________
> 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
3. 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
