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