HI Dana,
The CompiledQuery is intended for reuse, so the fact that you re-create it
for every query is the crux of the issue here. If you don't intend to reuse
the query, then you don't have any benefit in compiling it. So your
solutions could either be:
(1) Don't use compiled query, just execute a normal query.
(2) Save the compiled query and reuse it across many/all requests. This
will likely be higher performing but of course will make the web layer more
stateful. One nice thing I'll add though is that compiled queries are
thread safe, so you don't have to worry about race conditions or such.
Best regards,
Kasper
Den fre. 22. feb. 2019 kl. 12.57 skrev Dana Borger <dana.bor...@sas.com>:
>
> Hi group --
>
> We have a service that’s doing high volumes of queries using
> JdbcCompiledQuery. On MM 5.0.
>
> Our usage is basically: requests come in via REST, then the following code
> is done, and the result returned. We’re not running queries in a loop or
> re-using JdbcCompiledQuery objects, they just get created and closed and
> fall out of scope after each request. There are multiple threads doing same
> thing, but potentially with different queries.
>
>
> try (CompiledQuery compiledQuery = dataContext.compileQuery(...) { //
> dataContext is JdbcDataContext
> result = doExecute(compiledQuery, ...);
> }
>
> This closes the compiled-query, which in turn closes the generic object
> pool inside it, which has an evictor / timer thread inside.
>
> The issue we’re observing is under certain heavy loads we start piling up
> new Java timer threads by the thousands, eventually leading to “cannot
> create thread” exceptions. This seems to be timing dependent, probably due
> to the Evictor’s timer and this always-closing of the compiled-query.
>
> So, we’re looking for some guidance on this. For example, what use cases
> make the pool inside JdbcCompiledQuery beneficial?
>
> Our use case doesn’t seem that unusual, and we’ll need to fix it somehow.
> We tried a patched version of JdbcCompiledQuery that doesn’t use an
> object-pool+timer and that seems to fix the thread issue w/o any new
> negative effects.
>
> Thanks much for any advice,
> Dana
>
>
