pagination support at delegator level

[Harmeet Bedi]

I was trying to find a way to get chunked data from the delegator. I was
a bit surprised to not be able to get chunks easily and have it work
with caching. This seems like a basic thing, so maybe it exists. If it
does please point me in right direction.


In any case while digging, it appears that this is the state :
GenericDelegator::findList seems to be the method that things converge
to. This creates transaction boundaries ( so better to use than direct
GenericDelegator::find that returns unclosed result set/db handles). The
caching in this seems to be list specific. So individual object lookup
would not be retrieved from cache even though entity itself may be
cached as part of list.

A good set of improvements, i was thinking of were
1. Add another GenericDelegator::findList method that has two new
parameters - offset and size of entities requested. offset would start
from 1 to be compatible with EntityListIterator::getPartialList method.
2. Change caching to use key with offset and start. This will allow
caching to work with more than one list. Necessary for efficient page
based browsing.
3. Change caching to create cache both list and individual items in
list. Maybe a good idea to add another parameter as per advice. new
parameter could be called 'boolean cacheItems'. Advantage is that if one
drills down in paged/list browsing, lookup can be got from cache.

So something like this additional method signature in GenericAdapter

    public List<GenericValue> findList(String entityName,
EntityCondition entityCondition,
            Set<String> fieldsToSelect, List<String> orderBy,
EntityFindOptions findOptions, boolean useCache
// new parameters
            ,boolean cacheItems, int start,int size)

Caching would need to work with webtools.. so a bit more complex to get
paging work with caches. May be best to not do it in first pass. A lot
of methods don't set useCache = true in GenericAdapter.

Does this direction seem useful ?


One other thing i was looking at was, to call
java.sql.Statement::setMaxRows(int rows) when partial data set is requested.
The advantage being, that if only 10 rows are needed and ResultSet
fetchSize is 100.. No need to get the extra 90. This is a less straight
forward to do (for me) as the statement is buried through SQLProcessor..
also not sure if others have look into this or profiled this type of db
optimizations.

comments...
Harmeet

[Harmeet Bedi]

Looks like i should have looked more at EntityFindOptions.

Caching improvements would be worth doing perhaps.


On 21/06/2008 9:54 PM, Harmeet Bedi wrote:

> I was trying to find a way to get chunked data from the delegator. I
> was a bit surprised to not be able to get chunks easily and have it
> work with caching. This seems like a basic thing, so maybe it exists.
> If it does please point me in right direction.
>
>
> In any case while digging, it appears that this is the state :
> GenericDelegator::findList seems to be the method that things converge
> to. This creates transaction boundaries ( so better to use than direct
> GenericDelegator::find that returns unclosed result set/db handles).
> The caching in this seems to be list specific. So individual object
> lookup would not be retrieved from cache even though entity itself may
> be cached as part of list.
>
> A good set of improvements, i was thinking of were
> 1. Add another GenericDelegator::findList method that has two new
> parameters - offset and size of entities requested. offset would start
> from 1 to be compatible with EntityListIterator::getPartialList method.
> 2. Change caching to use key with offset and start. This will allow
> caching to work with more than one list. Necessary for efficient page
> based browsing.
> 3. Change caching to create cache both list and individual items in
> list. Maybe a good idea to add another parameter as per advice. new
> parameter could be called 'boolean cacheItems'. Advantage is that if
> one drills down in paged/list browsing, lookup can be got from cache.
>
> So something like this additional method signature in GenericAdapter
>
>    public List<GenericValue> findList(String entityName,
> EntityCondition entityCondition,
>            Set<String> fieldsToSelect, List<String> orderBy,
> EntityFindOptions findOptions, boolean useCache
> // new parameters
>            ,boolean cacheItems, int start,int size)
>
> Caching would need to work with webtools.. so a bit more complex to
> get paging work with caches. May be best to not do it in first pass. A
> lot of methods don't set useCache = true in GenericAdapter.
>
> Does this direction seem useful ?
>
>
> One other thing i was looking at was, to call
> java.sql.Statement::setMaxRows(int rows) when partial data set is
> requested.
> The advantage being, that if only 10 rows are needed and ResultSet
> fetchSize is 100.. No need to get the extra 90. This is a less
> straight forward to do (for me) as the statement is buried through
> SQLProcessor.. also not sure if others have look into this or profiled
> this type of db optimizations.
>
> comments...
> Harmeet

   1.