Use LIMIT X, Y for list.subList() on immutable extra-lazy collections with @OrderBy ----------------------------------------------------------------------------------- Key: HHH-6904 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-6904 Project: Hibernate Core Issue Type: Improvement Components: core Affects Versions: 3.6.5 Reporter: Bogdan Butnaru Priority: Minor As far as I know, an entity list marked with {{(a)LazyCollection(LazyCollectionOption.EXTRA)}} will still load the entire collection unless {{javax.persistence.@OrderColumn}} is used. The trouble is that @OrderColumn must be an index. It would be nice if Hibernate took advantage of {{org.hibernate.annotations.OrderBy}} and the various paging features of databases to do the same thing. Consider the configuration below: {code} @Entity class A { ... } @Entity class B { @Immutable @OneToMany @LazyCollection(LazyCollectionOption.EXTRA) @JoinTable(name = "a_of_b") @OrderBy(clause = "timestamp DESC") List<A> listOfA; } List<A> firstA(B b, int max){ List<A> list = b.listOfA(); return list.subList(0, Math.min(list.size(), max)); } {code} In this case, Hibernate loads the entire list, even if max is much smaller. It generates something like: {code} SELECT a.* FROM a JOIN a_of_b j ON a.id = j.a_id WHERE j.id = ? ORDER BY timestamp DESC {code} when instead it could do: {code} SELECT a.* FROM a JOIN a_of_b j ON a.id = j.a_id WHERE j.id = ? ORDER BY timestamp DESC LIMIT ?, ? {code} with the {{LIMIT}} set to whatever was passed to {{subList}}. (Except that if it already loaded some overlapping slices of the list before it could skip those parts.) I haven't thought a lot about how updates should be handled, but at least for the case of immutable collections it should work nicely. I haven't worked much with cursors, but AFAIK the same thing could be done for databases that support them. -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira