Hi,
i have a query which does need quite a bit of time (5 secs - up to 1000 items) and i want to improve the performace for pagination.

So i thought about fire this query just one time, and then save the IDs of this query in a Least Recently Used cache (LRU) with the query object as the key.

Now, if the same query is needed ( User wants the next page or re-orders the result ) i first check if this query is in the cache.
If true, i take the list of IDs and make a WHERE .. FROM .. IN query:

List result = session
.createQuery("FROM .. WHERE .. IN (" + cache.getIdStringList(query) + ")")
.setFirstResult(from)
.setMaxResults(max)
.list();


My question:
- does this approach maybe overrides a hibernate built in caching
- does this approach scale if if have more results than 1000 ( i think theres a limit of the number of WHRERE IN ( .. ) items)

or

how are you handling pagination for complex queries ?
i think the constraints .setFirstResult(from) .setMaxResults(max) do have no affect on the speed of the query or am i wrong with that ?


thanx for any hints,
chris