aspose file tools*
The moose likes Object Relational Mapping and the fly likes Help with JPA query please... Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Help with JPA query please..." Watch "Help with JPA query please..." New topic
Author

Help with JPA query please...

Caine Lai
Greenhorn

Joined: Sep 21, 2005
Posts: 15
I would be very grateful if someone were to help me with this JPA query. The object structure is simple. I have sessions and I have tasks. I would like to find the latest Task for a given Session.

So far, I have been able to do it with this query:



The problem is that if a session has many tasks (say 30,000 or so) than the query takes a VERY long time to process (30-60 seconds). I would like to optimize the sub-query to get rid of the max statement since that is what is expensive.

I would LIKE to do:



But I guess you cannot do a LIMIT clause in a sub-query. Can anyone think of a more performant solution to my problem?

Thanks!
Bauke Scholtz
Ranch Hand

Joined: Oct 08, 2006
Posts: 2458
Is the ID indexed? Are you familiar with the SQL JOIN clause?
Caine Lai
Greenhorn

Joined: Sep 21, 2005
Posts: 15
Thanks for the response. Yes, the id is indexed but apparently "max" still performs a full scan on MySQL. I am familiar with the SQL JOIN, yes. But I'm not sure how to do this in JPA.

In SQL I would do:



But I'm not sure how to write this in JPA.
Bauke Scholtz
Ranch Hand

Joined: Oct 08, 2006
Posts: 2458
No, I mean a JOIN instead of a subquery.

A JOIN in JPQL is similar to a JOIN in SQL, you can just leave the ON part away.

And, still unanswered, is the ID field indexed? It matters a lot.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Help with JPA query please...