Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Help with JPA query please...

 
Caine Lai
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2458
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is the ID indexed? Are you familiar with the SQL JOIN clause?
 
Caine Lai
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2458
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic