Thanks! I definitely am aware of 2nd level cache and have not used yet...I don't think, even though I'm using Eclipselink JPA (2nd) level cache (query results cache and statement cache) and it is working great.rikup wrote:Just a suggestion, have you heard of second-level caches with JPA? If you haven't I really suggest that you take a look on them. I personally use PostgreSQL with Hibernate + second level cache and it's really fast setup. And the spped comes from second level cache, not PostgreSQL Of course there are drawbacks with second level cache but in general it's definitely worth looking.
I just finished sending an email to myself after monitoring a SLOW SQL QUERY perform via Java Visual VM on the production server, and I just recognized that that QUERY is one of the main reasons why I mentioned the database 'may' be the only bottleneck.
What the real bottleneck is my 'use' of JPA/derby-database. I have tuned the database queries and added indexes, but having DISTINCT, NOT EXISTS, and ORDER BY will not always perform very well.
For example, the name query below takes 609 ms (which populates the datatable on the page)
Code: Select all
@NamedQuery(name = "AuditTrail.findAllAuditTrail",
query = "SELECT a FROM AuditTrail a JOIN a.orders o WHERE " +
"(a.auditTrailDt BETWEEN :from AND :to) AND " +
"NOT EXISTS (SELECT ot FROM OrderTemplate ot WHERE ot.templateId = o.templateId.templateId) " +
"ORDER BY a.auditTrailDt DESC"),
Code: Select all
@NamedQuery(name = "AuditTrail.findOrdersForAuditTrail",
query = "SELECT DISTINCT o FROM AuditTrail a JOIN a.orders o WHERE " +
"(a.auditTrailDt BETWEEN :from AND :to) AND " +
"NOT EXISTS (SELECT ot FROM OrderTemplate ot WHERE ot.templateId = o.templateId.templateId) " +
"ORDER BY o.reportDateTime, o.tripDateTime, o.customerId.customerName")})
Code: Select all
@NamedQuery(name = "AuditTrail.findAllAuditTrailByOrderId",
query = "SELECT a FROM AuditTrail a JOIN a.orders o WHERE " +
"(a.auditTrailDt BETWEEN :from AND :to) AND " +
"(o.orderId = :orderId) AND " +
"NOT EXISTS (SELECT ot FROM OrderTemplate ot WHERE ot.templateId = o.templateId.templateId) " +
"ORDER BY a.auditTrailDt DESC"),
I love improving the performance of my app...I really do!!! right now, this is 'one' of the slowest queries in my app. there are other slow queries as well that I'm working on, just need to take time to improve them or think of the best way to improve the performance.
thanks for the off-topic response though!