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?