pagination support at delegator level

[Harmeet Bedi <ha...@gmail.com>]

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

Re: pagination support at delegator level

[Harmeet Bedi <ha...@gmail.com>]

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.