On Fri, Apr 4, 2014 at 6:39 AM, Howard W. Smith, Jr. <smithh032...@gmail.com
> wrote:
>
> On Fri, Apr 4, 2014 at 5:46 AM, ivan nikitsenka <nikitse...@gmail.com>wrote:
>
>>
>> <query>
>> <description></description>
>> <query-method>
>> <method-name>findAll</method-name>
>> <method-params></method-params>
>> </query-method>
>> <ejb-ql>select object(o) from BreakReason o order by
>> o.reasonDesc</ejb-ql>
>> </query>
>
>
> Seriously? ORDER BY a 'reasonDesc'?
>
> Every now and then, I am opening jvisualvm (Java Visual VM) and profiling
> (or checking the performance) on certain parts of my app, and more and
> more, I am removing,
>
> ORDER BY ...
>
> from my queries, and reverting to Collections.sort() + Comparator.
>
> Is there an index on this 'reasonDesc'? What is 'reasonDesc'.length(), on
> average?
>
> If this is the reason why TomEE [and your app] is degrading, then please
> change your query, and remove the ORDER BY ..., and sort your data some
> other way.
>
> Refer to your database tuning document. I use Apache Derby, and they have
> a very nice/comprehensive 'Tuning Derby' guide that I have referred to in
> the past, when my app went to production.
>
>
I have seen/heard MyFaces and/or TomEE committers say/recommend that they
do a lot of 'caching' to improve the performance of their app. Caching
what? Caching data that can [or should] be cached.
Can the result set of the following be cached in an @ApplicationScoped bean
(when app starts OR on the first time that the query is needed/executed)?
<ejb-ql>select object(o) from BreakReason o order by o.reasonDesc</ejb-ql>
In my app, there are a few queries, where the result set is used in a
dropdown (or JSF selectOneMenu). I cache the result set of those queries in
@PostConstruct of my [one and only] @ApplicationScoped bean.
Will TomEE [or my/your app] 'degrade' when caching such result sets? No,
because TomEE [or my/your app] will not need [or refer to] a @Stateless
@EJB to get this data during runtime...so many times.
Also to clarify what I said earlier about removing ORDER BY ...,
yes, I have some quite complex SQL, and when I profile the app (via
jvisualvm), I find that Apache Derby source code is not performing well
with [my] SQL and/or [my] database design, etc...
So, I replace ORDER BY ... with Collections.sort() + Comparator.
Also, as Jean-Louis asked/recommended, I have a [small] statement cache
setting on my database <Resource .../>
<Resource id="jdbc/mcmsJta" type="javax.sql.DataSource">
JdbcDriver org.apache.derby.jdbc.EmbeddedDriver
JdbcUrl jdbc:derby:C:/javadb/databases/...;create=true
...
jdbcInterceptors=StatementCache(max=128)
</Resource>
Also, I am using JPA @NamedQueries on many of the popular/most-used queries
in the app, especially those that are more complex and don't [always]
perform well.
Also, my JPA = EclipseLink, and I use query hints, see below.
public List<Driver> findAllAvailableSelectOne() {
return em.createNamedQuery("Driver.findAllAvailableSelectOne")
.setHint("eclipselink.query-results-cache", "true")
.setHint("eclipselink.read-only", "true")
.getResultList();
}
where the @NamedQuery is defined as follows:
@NamedQuery(name = "Driver.findAllAvailableSelectOne", query = "SELECT
d FROM Driver d WHERE d.includeInLookupList = 'Y' ORDER BY d.driverName"),
Interesting, I could probably cache the result set of this query in my
@ApplicationScoped bean, but d.includeInLookupList can/may be changed by
enduser, sometime during runtime, but enduser does not change that value
unless Driver/Employee is terminated or hired. :)
I may have to write some code to cache this result set in my
@ApplicationScoped bean, and if enduser changes any 'Driver' in the
database, then I will have to update the List<Driver> in my
@ApplicationScoped bean. Hmmm, thanks for calling my attention to this
query in my app. :)
