Problem Using Layout

UI Components for JSF
smithh032772
Posts: 6144
Joined: 10 Sep 2011, 21:10

11 Feb 2013, 20:13

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.
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.

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"),
while this query (populates a selectOneMenu, which contributes to filtering data in the datatable) takes 25082 ms

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")})
when the user selects an option in the selectOneMenu (mentioned above), the query below is executed; i have 'not' timed this query, because this query performs just as fast as the first query above

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"),
One thing that I have done, in some cases, is move the sorting to the java bean/controller, because even if I am selecting a bunch of data, I can use Comparator to sort data in the java bean/controller and improve the performance big time. I may do that for the 2nd query above. Have not decided yet. I just had some free time and I just finished sending that email to myself, and it is a coincidence that I'm analyzing that and you sending me an off-topic here... great minds think alike... our minds are on the same thing.

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! :)
Last edited by smithh032772 on 12 Feb 2013, 06:58, edited 1 time in total.
Howard

PrimeFaces 6.0, Extensions 6.0.0, Push (Atmosphere 2.4.0)
TomEE+ 1.7.4 (Tomcat 7.0.68), MyFaces Core 2.2.9, JDK8
JUEL 2.2.7 | OmniFaces | EclipseLink-JPA/Derby | Chrome

Java EE 6 Tutorial|NetBeans|Google|Stackoverflow|PrimeFaces|Apache

kukeltje
Expert Member
Posts: 9605
Joined: 17 Jun 2010, 13:34
Location: Netherlands

11 Feb 2013, 20:48

I'm waiting for Ronald to reply to these responses. Come on, Ronald! :)
Sorry, no... ;-P

smithh032772
Posts: 6144
Joined: 10 Sep 2011, 21:10

11 Feb 2013, 20:53

kukeltje wrote:Sorry, no... ;-P
I love it! I knew you would respond!!!! hahahaha! thanks! :)
Howard

PrimeFaces 6.0, Extensions 6.0.0, Push (Atmosphere 2.4.0)
TomEE+ 1.7.4 (Tomcat 7.0.68), MyFaces Core 2.2.9, JDK8
JUEL 2.2.7 | OmniFaces | EclipseLink-JPA/Derby | Chrome

Java EE 6 Tutorial|NetBeans|Google|Stackoverflow|PrimeFaces|Apache

Post Reply

Return to “PrimeFaces”

  • Information
  • Who is online

    Users browsing this forum: No registered users and 15 guests