• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Help with JPA query please...

 
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!
 
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.
 
Don't get me started about those stupid light bulbs.
    Bookmark Topic Watch Topic
  • New Topic