4 Replies Latest reply: Oct 22, 2012 2:13 AM by gimbal2 RSS

    certain jpa query takes too much time

    jmz931
      hello all, I have a problem with a jpa query. I'm working with glassfish 2.1.1 as the app server and toplink essentials as the jpa provider (default), and also with an oracle 11g database. The problem appears when I query large amounts of data and only when the query does 5 inner joins among tables (the query is dynamic in where conditions and searchs between a date range)

      I take the time at the ejb that calls it, and the times are (considering the same date range):
      - 4 inner joins query: 157ms to retrieve 12824 records
      - 5 inner joins query: *322523ms* to retrieve 1527 records

      when I use jdbc with the same query in plain sql, it took only 466ms.. the same in SQL Developer. I also have the problem that glassfish doesn't give any response until it finishes the query :( I run the asadmin generate-jvm-report when this happens and there is no deadlock..

      I have tested other oracle driver and also other jpa providers like eclipselink 1.2 but the problem remains.. I'm trying to test jpa 2.0 providers with no luck.. (glassfish 2.1.1 comes with jpa 1.0 support by default)

      any ideas? is it a jpa 1.0 limitation? the app uses a lot of jpa queries so change them to jdbc is not feasible
      thanks in advance

      jedo
        • 1. Re: certain jpa query takes too much time
          gimbal2
          There is no such thing as a JPA limitation - it is only an API, it doesn't actually do anything. If there is a problem in the JPA layer it is in Toplink, the IMPLEMENTATION of the API. Generally you won't get answers about specific products here. You could try swapping out Toplink for something like Hibernate temporarily to see if that makes any change.

          But JPA itself is only a front; eventually what goes to the database and what can cause a performance problem is still an SQL query. Most JPA providers have a way to at least partially log out the SQL query generated and from the Oracle side you should also be able to see it in logs; that is where you will need to figure out what is going on. Generally you need to add an index so a full table scan or table lock can be prevented; that is query and database structure specific.
          • 2. Re: certain jpa query takes too much time
            jmz931
            mmm.. I tried out many jpa providers, including jpa2.0, but the problem remained. Finally, I changed the query.. actually divided it in other queries that were more expensive as a whole... and worked :S

            the weird thing is that the original query (the one that was logged by the jpa provider) executes fine in SQL Developer.. anyway, what is perturbing is the fact that a single query can freeze glassfish
            • 3. Re: certain jpa query takes too much time
              r035198x
              Maybe this artilce will provide some helpful pointers: http://java-persistence-performance.blogspot.com/2011/06/how-to-improve-jpa-performance-by-1825.html
              • 4. Re: certain jpa query takes too much time
                gimbal2
                user1107924 wrote:
                anyway, what is perturbing is the fact that a single query can freeze glassfish
                It cannot. So either:

                a) you misinterpret what you see happening
                b) you have a REALLY broken JDBC driver