Speeding up reporting queries with size() on list

Hi,

I'm working on some reporting stuff in our application. One of few different reports should include activity on last modified objects. The query for now looks like this:

select date(insp.lastModificationDate), insp.modifiedBy, insp.description.length(), insp.products.size(), insp.picture
from Inspiration insp
order by date(insp.lastModificationDate) desc

insp.description is normal String field in Inspiration entity. insp.products is a list with 0 to 600 objects. Definition:

@ManyToMany(targetEntity = Product.class, fetch = FetchType.LAZY)
private List<Product> products;

There is only 680 Inspiration entities in database right now. With 'cold' db file execution time of this query is between 1.9 sec and 2.8 sec. On 'hot' db file  without query result in cache, execution time is between 500ms and 1.2 sec. The same query without counting products runs in 15-30ms each time.

My question is - how to speed this type of queries, where you have to count objects on some lists?

Please try build 2.3.5_03 that includes a new optimization for this case.

First few tests shows, that new version works great (2sec queries now run in about 90ms). I will do some more testing in next few days.