2 Replies Latest reply: Sep 11, 2012 6:41 AM by james_sutherland RSS

    JPA  10X SLOWER than JDBC on a simple count query.

      I have weblogic 11g set up and noticed some JPA queries were running slow. I set up a data source and when I connect to the data source running a "select count(*) from table" query, it takes 9 seconds to complete.
      If I run the same query with JPA with a named query "select count(o) from table o" it takes 109 seconds. Both examples use the same data source connection so it seems to just be JPA itself.

      I have put logging statements directly before and after em.createNamedQuery(...) and this method call itself is the culprit. What can JPA Possibly be doing for 100 seconds?

      If anyone can provide any information as to how to speed this up or diagnose it better I would apperciate it.
        • 1. Re: JPA  10X SLOWER than JDBC on a simple count query.
          There are a number of docs on how to profile EclipseLink to find out where time is being spent. Here is a link on overall performance:

          Here is one specific to the EclipseLink profiler:
          The profiler can tell you where each query is spending its time.

          Please also note that you may want to turn on logging to see the SQL that actually gets generated for your JPQL query so you can see if there is a difference that might account for added time in the database statement. EclipseLink logging is described here: http://wiki.eclipse.org/EclipseLink/Examples/JPA/Logging . Remember to turn it off though for time comparisons as it will slow down the JPA query.

          Please also remember that the database might be using a prepared statement for your JDBC query while have to parse the JPQL query statement the first time. Try executing the query multiple times to see how the performance changes.

          Best Regards,
          • 2. Re: JPA  10X SLOWER than JDBC on a simple count query.
            109 seconds makes no sense. Even 9 seconds for a simple count query seems very odd, unless the table is extremely huge. What kind of machine/environment are you running on? Or did you mean milliseconds?

            Is it the first time you have executed a query? It could be that the persistence unit is being initialized, or the JPQL parser classes may be being loaded.

            Try executing the query several times to see if it is just the first time.

            Also, try using a VM profiler such as JProfiler to see what is going on.