Idea: be able to specify size and index of entity lists

[Leon Torres-2]

Hi folks,

I think we really need to be able to specify the size of the list we want and
the index to start at for the GenericDelegator.findByAnd and findByCondition
methods.

The idea is to support pagination in the form widgets and similar systems for
lists of data that cannot be supported by <view-entity>.  For example, if the
inventory QOH and ATP are required for a form-widget list, we need to call the
getInventoryAvailableByFacility service and add the results to each list row.
Another example would be a union of various entities together, some of which
need heuristics to select the data.

It should be relatively simple: Create a method that wraps a call to
findListIteratorByCondition, then grab the desired range of results.  It should
also return the size of the table.

Then, as an example, we can call these methods with our viewSize and viewIndex
parameters, build our complex list of data based on the results, and use the
form-widget's override-list-size to make pagination work with it.

Thoughts?

- Leon

[jonwimp]

+1

Phew, somebody got to this. I'm not touching on generic pagination objects until this is done. :P
Ok, I'll help do it if need be. I've dug into that part of the framework lots by now.

Just FYI, I coded a somewhat similar entity framework in PHP last year, and it does have
provisions for SQL "Limit" (ranges). Yeah, it's mainly used for pagination, nothing else, I believe.

Jonathon

Leon Torres wrote:

> Hi folks,
>
> I think we really need to be able to specify the size of the list we
> want and the index to start at for the GenericDelegator.findByAnd and
> findByCondition methods.
>
> The idea is to support pagination in the form widgets and similar
> systems for lists of data that cannot be supported by <view-entity>.  
> For example, if the inventory QOH and ATP are required for a form-widget
> list, we need to call the getInventoryAvailableByFacility service and
> add the results to each list row. Another example would be a union of
> various entities together, some of which need heuristics to select the
> data.
>
> It should be relatively simple: Create a method that wraps a call to
> findListIteratorByCondition, then grab the desired range of results.  It
> should also return the size of the table.
>
> Then, as an example, we can call these methods with our viewSize and
> viewIndex parameters, build our complex list of data based on the
> results, and use the form-widget's override-list-size to make pagination
> work with it.
>
> Thoughts?
>
> - Leon
>
>

[David E Jones]

What's wrong with the stuff that's been there for years on the  
EntityListIterator?

-David


On Jan 30, 2007, at 4:52 PM, Leon Torres wrote:

> Hi folks,
>
> I think we really need to be able to specify the size of the list  
> we want and the index to start at for the  
> GenericDelegator.findByAnd and findByCondition methods.
>
> The idea is to support pagination in the form widgets and similar  
> systems for lists of data that cannot be supported by <view-
> entity>.  For example, if the inventory QOH and ATP are required  
> for a form-widget list, we need to call the  
> getInventoryAvailableByFacility service and add the results to each  
> list row. Another example would be a union of various entities  
> together, some of which need heuristics to select the data.
>
> It should be relatively simple: Create a method that wraps a call  
> to findListIteratorByCondition, then grab the desired range of  
> results.  It should also return the size of the table.
>
> Then, as an example, we can call these methods with our viewSize  
> and viewIndex parameters, build our complex list of data based on  
> the results, and use the form-widget's override-list-size to make  
> pagination work with it.
>
> Thoughts?
>
> - Leon

[Leon Torres-2]

Hi David,

Sorry, maybe I should have expressed this in the form of two questions.

Is there a way to do a query with OFFSET and LIMIT using EntityCondition?

If not, then is there a way to do these offset and limit operations with
findEntityListIteratorByCondition?

- Leon



David E. Jones wrote:

>
> What's wrong with the stuff that's been there for years on the
> EntityListIterator?
>
> -David
>
>
> On Jan 30, 2007, at 4:52 PM, Leon Torres wrote:
>
>> Hi folks,
>>
>> I think we really need to be able to specify the size of the list we
>> want and the index to start at for the GenericDelegator.findByAnd and
>> findByCondition methods.
>>
>> The idea is to support pagination in the form widgets and similar
>> systems for lists of data that cannot be supported by <view-entity>.  
>> For example, if the inventory QOH and ATP are required for a
>> form-widget list, we need to call the getInventoryAvailableByFacility
>> service and add the results to each list row. Another example would be
>> a union of various entities together, some of which need heuristics to
>> select the data.
>>
>> It should be relatively simple: Create a method that wraps a call to
>> findListIteratorByCondition, then grab the desired range of results.  
>> It should also return the size of the table.
>>
>> Then, as an example, we can call these methods with our viewSize and
>> viewIndex parameters, build our complex list of data based on the
>> results, and use the form-widget's override-list-size to make
>> pagination work with it.
>>
>> Thoughts?
>>
>> - Leon
>

[David E Jones]

Could you explain how that would be different and better than using  
the EntityListIterator.getPartialList method?

-David


On Jan 31, 2007, at 11:21 AM, Leon Torres wrote:

> Hi David,
>
> Sorry, maybe I should have expressed this in the form of two  
> questions.
>
> Is there a way to do a query with OFFSET and LIMIT using  
> EntityCondition?
>
> If not, then is there a way to do these offset and limit operations  
> with findEntityListIteratorByCondition?
>
> - Leon
>
>
>
> David E. Jones wrote:
>> What's wrong with the stuff that's been there for years on the  
>> EntityListIterator?
>> -David
>> On Jan 30, 2007, at 4:52 PM, Leon Torres wrote:
>>> Hi folks,
>>>
>>> I think we really need to be able to specify the size of the list  
>>> we want and the index to start at for the  
>>> GenericDelegator.findByAnd and findByCondition methods.
>>>
>>> The idea is to support pagination in the form widgets and similar  
>>> systems for lists of data that cannot be supported by <view-
>>> entity>.  For example, if the inventory QOH and ATP are required  
>>> for a form-widget list, we need to call the  
>>> getInventoryAvailableByFacility service and add the results to  
>>> each list row. Another example would be a union of various  
>>> entities together, some of which need heuristics to select the data.
>>>
>>> It should be relatively simple: Create a method that wraps a call  
>>> to findListIteratorByCondition, then grab the desired range of  
>>> results.  It should also return the size of the table.
>>>
>>> Then, as an example, we can call these methods with our viewSize  
>>> and viewIndex parameters, build our complex list of data based on  
>>> the results, and use the form-widget's override-list-size to make  
>>> pagination work with it.
>>>
>>> Thoughts?
>>>
>>> - Leon

[Leon Torres-2]

Ah yes, I forgot about that method.  Great, so it has the capability to handle
offset and limit.

But it still doesn't solve the problem I have, which is simply that it's very
cumbersome to use this in conjunction with findEntityListIterator.  In fact, it
makes things worse.

1)  That this method considers 1 to be the index of the first element of the
list is guaranteed to throw developers off.

2)  My idea was to create convenience methods in GenericDelegator which
basically wraps a call to findListIteratorByCondition and then does the
getPartialList for you so you don't have to worry about issue #1 and helps
satisfy the Don't Repeat Yourself (DRY) principle.

List whatINeed = delegator.findByAnd(entityName, fields, start, end);

List alsoWhatINeed = delegator.findByCondition(entityName, fields, conditions,
orderBy, start, end);

Start and end would use the traditional notion of 0 being the start index.

Does my idea make sense now?  It's for convenience and to hide some of the
complexity.

- Leon


David E. Jones wrote:

>
> Could you explain how that would be different and better than using the
> EntityListIterator.getPartialList method?
>
> -David
>
>
> On Jan 31, 2007, at 11:21 AM, Leon Torres wrote:
>
>> Hi David,
>>
>> Sorry, maybe I should have expressed this in the form of two questions.
>>
>> Is there a way to do a query with OFFSET and LIMIT using EntityCondition?
>>
>> If not, then is there a way to do these offset and limit operations
>> with findEntityListIteratorByCondition?
>>
>> - Leon
>>
>>
>>
>> David E. Jones wrote:
>>> What's wrong with the stuff that's been there for years on the
>>> EntityListIterator?
>>> -David
>>> On Jan 30, 2007, at 4:52 PM, Leon Torres wrote:
>>>> Hi folks,
>>>>
>>>> I think we really need to be able to specify the size of the list we
>>>> want and the index to start at for the GenericDelegator.findByAnd
>>>> and findByCondition methods.
>>>>
>>>> The idea is to support pagination in the form widgets and similar
>>>> systems for lists of data that cannot be supported by
>>>> <view-entity>.  For example, if the inventory QOH and ATP are
>>>> required for a form-widget list, we need to call the
>>>> getInventoryAvailableByFacility service and add the results to each
>>>> list row. Another example would be a union of various entities
>>>> together, some of which need heuristics to select the data.
>>>>
>>>> It should be relatively simple: Create a method that wraps a call to
>>>> findListIteratorByCondition, then grab the desired range of
>>>> results.  It should also return the size of the table.
>>>>
>>>> Then, as an example, we can call these methods with our viewSize and
>>>> viewIndex parameters, build our complex list of data based on the
>>>> results, and use the form-widget's override-list-size to make
>>>> pagination work with it.
>>>>
>>>> Thoughts?
>>>>
>>>> - Leon
>

[Leon Torres-2]

Oh yeah, nearly forgot.  The biggest problem was not having the size of the
table on hand.  We need to set the override-list-size form variable somehow, and
my method was just getting to be very repetitive, violating DRY principle.

So these methods need to return the list size as well.

- Leon



Leon Torres wrote:

> Ah yes, I forgot about that method.  Great, so it has the capability to
> handle offset and limit.
>
> But it still doesn't solve the problem I have, which is simply that it's
> very cumbersome to use this in conjunction with findEntityListIterator.  
> In fact, it makes things worse.
>
> 1)  That this method considers 1 to be the index of the first element of
> the list is guaranteed to throw developers off.
>
> 2)  My idea was to create convenience methods in GenericDelegator which
> basically wraps a call to findListIteratorByCondition and then does the
> getPartialList for you so you don't have to worry about issue #1 and
> helps satisfy the Don't Repeat Yourself (DRY) principle.
>
> List whatINeed = delegator.findByAnd(entityName, fields, start, end);
>
> List alsoWhatINeed = delegator.findByCondition(entityName, fields,
> conditions, orderBy, start, end);
>
> Start and end would use the traditional notion of 0 being the start index.
>
> Does my idea make sense now?  It's for convenience and to hide some of
> the complexity.
>
> - Leon
>
>
> David E. Jones wrote:
>>
>> Could you explain how that would be different and better than using
>> the EntityListIterator.getPartialList method?
>>
>> -David
>>
>>
>> On Jan 31, 2007, at 11:21 AM, Leon Torres wrote:
>>
>>> Hi David,
>>>
>>> Sorry, maybe I should have expressed this in the form of two questions.
>>>
>>> Is there a way to do a query with OFFSET and LIMIT using
>>> EntityCondition?
>>>
>>> If not, then is there a way to do these offset and limit operations
>>> with findEntityListIteratorByCondition?
>>>
>>> - Leon
>>>
>>>
>>>
>>> David E. Jones wrote:
>>>> What's wrong with the stuff that's been there for years on the
>>>> EntityListIterator?
>>>> -David
>>>> On Jan 30, 2007, at 4:52 PM, Leon Torres wrote:
>>>>> Hi folks,
>>>>>
>>>>> I think we really need to be able to specify the size of the list
>>>>> we want and the index to start at for the
>>>>> GenericDelegator.findByAnd and findByCondition methods.
>>>>>
>>>>> The idea is to support pagination in the form widgets and similar
>>>>> systems for lists of data that cannot be supported by
>>>>> <view-entity>.  For example, if the inventory QOH and ATP are
>>>>> required for a form-widget list, we need to call the
>>>>> getInventoryAvailableByFacility service and add the results to each
>>>>> list row. Another example would be a union of various entities
>>>>> together, some of which need heuristics to select the data.
>>>>>
>>>>> It should be relatively simple: Create a method that wraps a call
>>>>> to findListIteratorByCondition, then grab the desired range of
>>>>> results.  It should also return the size of the table.
>>>>>
>>>>> Then, as an example, we can call these methods with our viewSize
>>>>> and viewIndex parameters, build our complex list of data based on
>>>>> the results, and use the form-widget's override-list-size to make
>>>>> pagination work with it.
>>>>>
>>>>> Thoughts?
>>>>>
>>>>> - Leon
>>
>

[jonwimp]

I could be wrong, but it seems OFBiz pulls down all records (1000s possibly) before putting all
those records through that function. Yeah, I know, the partial list is gleaned off of the
resultset and so it seems that we're not exactly swallowing all records first.

But isn't it more database-independent (or less?) to use SQL LIMIT?

I'm seeing long load times for LookUpProduct service when listing for pagination a mere 1000
records. Perhaps it'll be much much faster using SQL LIMIT? I know for a fact that using SQL LIMIT
is faster than scrolling through a resultset, know that based on my own apps (currently having
entity framework that provides for SQL LIMIT).

Jonathon

David E. Jones wrote:

>
> Could you explain how that would be different and better than using the
> EntityListIterator.getPartialList method?
>
> -David
>
>
> On Jan 31, 2007, at 11:21 AM, Leon Torres wrote:
>
>> Hi David,
>>
>> Sorry, maybe I should have expressed this in the form of two questions.
>>
>> Is there a way to do a query with OFFSET and LIMIT using EntityCondition?
>>
>> If not, then is there a way to do these offset and limit operations
>> with findEntityListIteratorByCondition?
>>
>> - Leon
>>
>>
>>
>> David E. Jones wrote:
>>> What's wrong with the stuff that's been there for years on the
>>> EntityListIterator?
>>> -David
>>> On Jan 30, 2007, at 4:52 PM, Leon Torres wrote:
>>>> Hi folks,
>>>>
>>>> I think we really need to be able to specify the size of the list we
>>>> want and the index to start at for the GenericDelegator.findByAnd
>>>> and findByCondition methods.
>>>>
>>>> The idea is to support pagination in the form widgets and similar
>>>> systems for lists of data that cannot be supported by
>>>> <view-entity>.  For example, if the inventory QOH and ATP are
>>>> required for a form-widget list, we need to call the
>>>> getInventoryAvailableByFacility service and add the results to each
>>>> list row. Another example would be a union of various entities
>>>> together, some of which need heuristics to select the data.
>>>>
>>>> It should be relatively simple: Create a method that wraps a call to
>>>> findListIteratorByCondition, then grab the desired range of
>>>> results.  It should also return the size of the table.
>>>>
>>>> Then, as an example, we can call these methods with our viewSize and
>>>> viewIndex parameters, build our complex list of data based on the
>>>> results, and use the form-widget's override-list-size to make
>>>> pagination work with it.
>>>>
>>>> Thoughts?
>>>>
>>>> - Leon
>

[David E Jones]

This is incorrect. The EntityListIterator uses a database cursor and  
keeps the connection open to the database. Depending on what the  
database and JDBC driver support and how things are configured, it  
will typically pull over 100 records at a time over the network as it  
scrolls through or jumps around the result set.

As to your specific performance problems, with that information I  
have no idea what the problem might be. It depends on how you have  
deployed OFBiz, how the database and JDBC driver are setup, and if  
the custom or OOTB OFBiz code is written properly to use the  
EntityListIterator.

-David


On Jan 31, 2007, at 4:53 PM, Jonathon -- Improov wrote:

> I could be wrong, but it seems OFBiz pulls down all records (1000s  
> possibly) before putting all those records through that function.  
> Yeah, I know, the partial list is gleaned off of the resultset and  
> so it seems that we're not exactly swallowing all records first.
>
> But isn't it more database-independent (or less?) to use SQL LIMIT?
>
> I'm seeing long load times for LookUpProduct service when listing  
> for pagination a mere 1000 records. Perhaps it'll be much much  
> faster using SQL LIMIT? I know for a fact that using SQL LIMIT is  
> faster than scrolling through a resultset, know that based on my  
> own apps (currently having entity framework that provides for SQL  
> LIMIT).
>
> Jonathon
>
> David E. Jones wrote:
>> Could you explain how that would be different and better than  
>> using the EntityListIterator.getPartialList method?
>> -David
>> On Jan 31, 2007, at 11:21 AM, Leon Torres wrote:
>>> Hi David,
>>>
>>> Sorry, maybe I should have expressed this in the form of two  
>>> questions.
>>>
>>> Is there a way to do a query with OFFSET and LIMIT using  
>>> EntityCondition?
>>>
>>> If not, then is there a way to do these offset and limit  
>>> operations with findEntityListIteratorByCondition?
>>>
>>> - Leon
>>>
>>>
>>>
>>> David E. Jones wrote:
>>>> What's wrong with the stuff that's been there for years on the  
>>>> EntityListIterator?
>>>> -David
>>>> On Jan 30, 2007, at 4:52 PM, Leon Torres wrote:
>>>>> Hi folks,
>>>>>
>>>>> I think we really need to be able to specify the size of the  
>>>>> list we want and the index to start at for the  
>>>>> GenericDelegator.findByAnd and findByCondition methods.
>>>>>
>>>>> The idea is to support pagination in the form widgets and  
>>>>> similar systems for lists of data that cannot be supported by  
>>>>> <view-entity>.  For example, if the inventory QOH and ATP are  
>>>>> required for a form-widget list, we need to call the  
>>>>> getInventoryAvailableByFacility service and add the results to  
>>>>> each list row. Another example would be a union of various  
>>>>> entities together, some of which need heuristics to select the  
>>>>> data.
>>>>>
>>>>> It should be relatively simple: Create a method that wraps a  
>>>>> call to findListIteratorByCondition, then grab the desired  
>>>>> range of results.  It should also return the size of the table.
>>>>>
>>>>> Then, as an example, we can call these methods with our  
>>>>> viewSize and viewIndex parameters, build our complex list of  
>>>>> data based on the results, and use the form-widget's override-
>>>>> list-size to make pagination work with it.
>>>>>
>>>>> Thoughts?
>>>>>
>>>>> - Leon
>

[Adrian Crum]

I agree with David, plus here's some additional insight:

If you're talking about performance on a development machine, then it's
understandable that large datasets take time to load - since your development
machine is also acting as a database server. So, even if a jdbc driver uses
small result sets to scroll through a larger cursor, it's not going to benefit
you if it's all running on the same machine.



David E. Jones wrote:

>
> This is incorrect. The EntityListIterator uses a database cursor and  
> keeps the connection open to the database. Depending on what the  
> database and JDBC driver support and how things are configured, it  will
> typically pull over 100 records at a time over the network as it  
> scrolls through or jumps around the result set.
>
> As to your specific performance problems, with that information I  have
> no idea what the problem might be. It depends on how you have  deployed
> OFBiz, how the database and JDBC driver are setup, and if  the custom or
> OOTB OFBiz code is written properly to use the  EntityListIterator.
>
> -David
>
>
> On Jan 31, 2007, at 4:53 PM, Jonathon -- Improov wrote:
>
>> I could be wrong, but it seems OFBiz pulls down all records (1000s  
>> possibly) before putting all those records through that function.  
>> Yeah, I know, the partial list is gleaned off of the resultset and  so
>> it seems that we're not exactly swallowing all records first.
>>
>> But isn't it more database-independent (or less?) to use SQL LIMIT?
>>
>> I'm seeing long load times for LookUpProduct service when listing  for
>> pagination a mere 1000 records. Perhaps it'll be much much  faster
>> using SQL LIMIT? I know for a fact that using SQL LIMIT is  faster
>> than scrolling through a resultset, know that based on my  own apps
>> (currently having entity framework that provides for SQL  LIMIT).
>>
>> Jonathon
>>
>> David E. Jones wrote:
>>
>>> Could you explain how that would be different and better than  using
>>> the EntityListIterator.getPartialList method?
>>> -David
>>> On Jan 31, 2007, at 11:21 AM, Leon Torres wrote:
>>>
>>>> Hi David,
>>>>
>>>> Sorry, maybe I should have expressed this in the form of two  
>>>> questions.
>>>>
>>>> Is there a way to do a query with OFFSET and LIMIT using  
>>>> EntityCondition?
>>>>
>>>> If not, then is there a way to do these offset and limit  operations
>>>> with findEntityListIteratorByCondition?
>>>>
>>>> - Leon
>>>>
>>>>
>>>>
>>>> David E. Jones wrote:
>>>>
>>>>> What's wrong with the stuff that's been there for years on the  
>>>>> EntityListIterator?
>>>>> -David
>>>>> On Jan 30, 2007, at 4:52 PM, Leon Torres wrote:
>>>>>
>>>>>> Hi folks,
>>>>>>
>>>>>> I think we really need to be able to specify the size of the  list
>>>>>> we want and the index to start at for the  
>>>>>> GenericDelegator.findByAnd and findByCondition methods.
>>>>>>
>>>>>> The idea is to support pagination in the form widgets and  similar
>>>>>> systems for lists of data that cannot be supported by  
>>>>>> <view-entity>.  For example, if the inventory QOH and ATP are  
>>>>>> required for a form-widget list, we need to call the  
>>>>>> getInventoryAvailableByFacility service and add the results to  
>>>>>> each list row. Another example would be a union of various  
>>>>>> entities together, some of which need heuristics to select the  data.
>>>>>>
>>>>>> It should be relatively simple: Create a method that wraps a  call
>>>>>> to findListIteratorByCondition, then grab the desired  range of
>>>>>> results.  It should also return the size of the table.
>>>>>>
>>>>>> Then, as an example, we can call these methods with our  viewSize
>>>>>> and viewIndex parameters, build our complex list of  data based on
>>>>>> the results, and use the form-widget's override- list-size to make
>>>>>> pagination work with it.
>>>>>>
>>>>>> Thoughts?
>>>>>>
>>>>>> - Leon
>>
>>
>

[Leon Torres-2]

I agree with David too.

But back on topic, I have an idea.  I'll create a simple Util method that
expresses in code precisely what I need and where it's useful.

Will probably do this in CRMSFA because there's a few lists there that use the
override-list-size that could be refactored according to my ideas.

Then, if it actually proves to be useful, I'll create a JIRA issue and we can
carry on the discussion.

Thanks for your input. :-)

- Leon



Adrian Crum wrote:

> I agree with David, plus here's some additional insight:
>
> If you're talking about performance on a development machine, then it's
> understandable that large datasets take time to load - since your
> development machine is also acting as a database server. So, even if a
> jdbc driver uses small result sets to scroll through a larger cursor,
> it's not going to benefit you if it's all running on the same machine.
>
>
>
> David E. Jones wrote:
>>
>> This is incorrect. The EntityListIterator uses a database cursor and  
>> keeps the connection open to the database. Depending on what the  
>> database and JDBC driver support and how things are configured, it  
>> will typically pull over 100 records at a time over the network as it  
>> scrolls through or jumps around the result set.
>>
>> As to your specific performance problems, with that information I  
>> have no idea what the problem might be. It depends on how you have  
>> deployed OFBiz, how the database and JDBC driver are setup, and if  
>> the custom or OOTB OFBiz code is written properly to use the  
>> EntityListIterator.
>>
>> -David
>>
>>
>> On Jan 31, 2007, at 4:53 PM, Jonathon -- Improov wrote:
>>
>>> I could be wrong, but it seems OFBiz pulls down all records (1000s  
>>> possibly) before putting all those records through that function.  
>>> Yeah, I know, the partial list is gleaned off of the resultset and  
>>> so it seems that we're not exactly swallowing all records first.
>>>
>>> But isn't it more database-independent (or less?) to use SQL LIMIT?
>>>
>>> I'm seeing long load times for LookUpProduct service when listing  
>>> for pagination a mere 1000 records. Perhaps it'll be much much  
>>> faster using SQL LIMIT? I know for a fact that using SQL LIMIT is  
>>> faster than scrolling through a resultset, know that based on my  own
>>> apps (currently having entity framework that provides for SQL  LIMIT).
>>>
>>> Jonathon
>>>
>>> David E. Jones wrote:
>>>
>>>> Could you explain how that would be different and better than  using
>>>> the EntityListIterator.getPartialList method?
>>>> -David
>>>> On Jan 31, 2007, at 11:21 AM, Leon Torres wrote:
>>>>
>>>>> Hi David,
>>>>>
>>>>> Sorry, maybe I should have expressed this in the form of two  
>>>>> questions.
>>>>>
>>>>> Is there a way to do a query with OFFSET and LIMIT using  
>>>>> EntityCondition?
>>>>>
>>>>> If not, then is there a way to do these offset and limit  
>>>>> operations with findEntityListIteratorByCondition?
>>>>>
>>>>> - Leon
>>>>>
>>>>>
>>>>>
>>>>> David E. Jones wrote:
>>>>>
>>>>>> What's wrong with the stuff that's been there for years on the  
>>>>>> EntityListIterator?
>>>>>> -David
>>>>>> On Jan 30, 2007, at 4:52 PM, Leon Torres wrote:
>>>>>>
>>>>>>> Hi folks,
>>>>>>>
>>>>>>> I think we really need to be able to specify the size of the  
>>>>>>> list we want and the index to start at for the  
>>>>>>> GenericDelegator.findByAnd and findByCondition methods.
>>>>>>>
>>>>>>> The idea is to support pagination in the form widgets and  
>>>>>>> similar systems for lists of data that cannot be supported by  
>>>>>>> <view-entity>.  For example, if the inventory QOH and ATP are  
>>>>>>> required for a form-widget list, we need to call the  
>>>>>>> getInventoryAvailableByFacility service and add the results to  
>>>>>>> each list row. Another example would be a union of various  
>>>>>>> entities together, some of which need heuristics to select the  
>>>>>>> data.
>>>>>>>
>>>>>>> It should be relatively simple: Create a method that wraps a  
>>>>>>> call to findListIteratorByCondition, then grab the desired  range
>>>>>>> of results.  It should also return the size of the table.
>>>>>>>
>>>>>>> Then, as an example, we can call these methods with our  viewSize
>>>>>>> and viewIndex parameters, build our complex list of  data based
>>>>>>> on the results, and use the form-widget's override- list-size to
>>>>>>> make pagination work with it.
>>>>>>>
>>>>>>> Thoughts?
>>>>>>>
>>>>>>> - Leon
>>>
>>>
>>
>

[jonwimp]

Ok, just a quick question about MySQL here, in case anyone knows it well offhand. Does how does
MySQL cursors perform compared to using plain SQL LIMIT? Bump me off to a new thread if this is
gonna draw too many responses. (Is it off-topic?).

 > This is incorrect. The EntityListIterator uses a database cursor and
 > keeps the connection open to the database. Depending on what the
 > database and JDBC driver support and how things are configured, it will
 > typically pull over 100 records at a time over the network as it scrolls
 > through or jumps around the result set.

Sounds very database-dependent. Will it make things worse if we used SQL LIMIT?

 > As to your specific performance problems, with that information I have
 > no idea what the problem might be. It depends on how you have deployed
 > OFBiz, how the database and JDBC driver are setup, and if the custom or
 > OOTB OFBiz code is written properly to use the EntityListIterator.

I found the performance problem using low-level MySQL functions. I suspect MySQL (4.x) doesn't do
resultset scrolls as efficiently as when using SQL LIMIT, not sure.

Jonathon

David E. Jones wrote:

>
> This is incorrect. The EntityListIterator uses a database cursor and
> keeps the connection open to the database. Depending on what the
> database and JDBC driver support and how things are configured, it will
> typically pull over 100 records at a time over the network as it scrolls
> through or jumps around the result set.
>
> As to your specific performance problems, with that information I have
> no idea what the problem might be. It depends on how you have deployed
> OFBiz, how the database and JDBC driver are setup, and if the custom or
> OOTB OFBiz code is written properly to use the EntityListIterator.
>
> -David
>
>
> On Jan 31, 2007, at 4:53 PM, Jonathon -- Improov wrote:
>
>> I could be wrong, but it seems OFBiz pulls down all records (1000s
>> possibly) before putting all those records through that function.
>> Yeah, I know, the partial list is gleaned off of the resultset and so
>> it seems that we're not exactly swallowing all records first.
>>
>> But isn't it more database-independent (or less?) to use SQL LIMIT?
>>
>> I'm seeing long load times for LookUpProduct service when listing for
>> pagination a mere 1000 records. Perhaps it'll be much much faster
>> using SQL LIMIT? I know for a fact that using SQL LIMIT is faster than
>> scrolling through a resultset, know that based on my own apps
>> (currently having entity framework that provides for SQL LIMIT).
>>
>> Jonathon
>>
>> David E. Jones wrote:
>>> Could you explain how that would be different and better than using
>>> the EntityListIterator.getPartialList method?
>>> -David
>>> On Jan 31, 2007, at 11:21 AM, Leon Torres wrote:
>>>> Hi David,
>>>>
>>>> Sorry, maybe I should have expressed this in the form of two questions.
>>>>
>>>> Is there a way to do a query with OFFSET and LIMIT using
>>>> EntityCondition?
>>>>
>>>> If not, then is there a way to do these offset and limit operations
>>>> with findEntityListIteratorByCondition?
>>>>
>>>> - Leon
>>>>
>>>>
>>>>
>>>> David E. Jones wrote:
>>>>> What's wrong with the stuff that's been there for years on the
>>>>> EntityListIterator?
>>>>> -David
>>>>> On Jan 30, 2007, at 4:52 PM, Leon Torres wrote:
>>>>>> Hi folks,
>>>>>>
>>>>>> I think we really need to be able to specify the size of the list
>>>>>> we want and the index to start at for the
>>>>>> GenericDelegator.findByAnd and findByCondition methods.
>>>>>>
>>>>>> The idea is to support pagination in the form widgets and similar
>>>>>> systems for lists of data that cannot be supported by
>>>>>> <view-entity>.  For example, if the inventory QOH and ATP are
>>>>>> required for a form-widget list, we need to call the
>>>>>> getInventoryAvailableByFacility service and add the results to
>>>>>> each list row. Another example would be a union of various
>>>>>> entities together, some of which need heuristics to select the data.
>>>>>>
>>>>>> It should be relatively simple: Create a method that wraps a call
>>>>>> to findListIteratorByCondition, then grab the desired range of
>>>>>> results.  It should also return the size of the table.
>>>>>>
>>>>>> Then, as an example, we can call these methods with our viewSize
>>>>>> and viewIndex parameters, build our complex list of data based on
>>>>>> the results, and use the form-widget's override-list-size to make
>>>>>> pagination work with it.
>>>>>>
>>>>>> Thoughts?
>>>>>>
>>>>>> - Leon
>>
>

[phantom.coding]

Agree that EntityListIterator keeps database cursor open and uses that to retrive the results. Which means that we should use a scrollable cursor (correct me if I'm wrong!!). But when working with ORACLE this doesn't work. Oracle does not support scrollable cursors. It's the JDBC layer that simulates the scrollable cursor and not the DB server. So even if we fetch results 100 by 100 at JDBC level the full result set is fetched.

Pls correct me if i'm wrong

David E. Jones-2 wrote

This is incorrect. The EntityListIterator uses a database cursor and  
keeps the connection open to the database. Depending on what the  
database and JDBC driver support and how things are configured, it  
will typically pull over 100 records at a time over the network as it  
scrolls through or jumps around the result set.

As to your specific performance problems, with that information I  
have no idea what the problem might be. It depends on how you have  
deployed OFBiz, how the database and JDBC driver are setup, and if  
the custom or OOTB OFBiz code is written properly to use the  
EntityListIterator.

-David


On Jan 31, 2007, at 4:53 PM, Jonathon -- Improov wrote:

> I could be wrong, but it seems OFBiz pulls down all records (1000s  
> possibly) before putting all those records through that function.  
> Yeah, I know, the partial list is gleaned off of the resultset and  
> so it seems that we're not exactly swallowing all records first.
>
> But isn't it more database-independent (or less?) to use SQL LIMIT?
>
> I'm seeing long load times for LookUpProduct service when listing  
> for pagination a mere 1000 records. Perhaps it'll be much much  
> faster using SQL LIMIT? I know for a fact that using SQL LIMIT is  
> faster than scrolling through a resultset, know that based on my  
> own apps (currently having entity framework that provides for SQL  
> LIMIT).
>
> Jonathon
>
> David E. Jones wrote:
>> Could you explain how that would be different and better than  
>> using the EntityListIterator.getPartialList method?
>> -David
>> On Jan 31, 2007, at 11:21 AM, Leon Torres wrote:
>>> Hi David,
>>>
>>> Sorry, maybe I should have expressed this in the form of two  
>>> questions.
>>>
>>> Is there a way to do a query with OFFSET and LIMIT using  
>>> EntityCondition?
>>>
>>> If not, then is there a way to do these offset and limit  
>>> operations with findEntityListIteratorByCondition?
>>>
>>> - Leon
>>>
>>>
>>>
>>> David E. Jones wrote:
>>>> What's wrong with the stuff that's been there for years on the  
>>>> EntityListIterator?
>>>> -David
>>>> On Jan 30, 2007, at 4:52 PM, Leon Torres wrote:
>>>>> Hi folks,
>>>>>
>>>>> I think we really need to be able to specify the size of the  
>>>>> list we want and the index to start at for the  
>>>>> GenericDelegator.findByAnd and findByCondition methods.
>>>>>
>>>>> The idea is to support pagination in the form widgets and  
>>>>> similar systems for lists of data that cannot be supported by  
>>>>> <view-entity>.  For example, if the inventory QOH and ATP are  
>>>>> required for a form-widget list, we need to call the  
>>>>> getInventoryAvailableByFacility service and add the results to  
>>>>> each list row. Another example would be a union of various  
>>>>> entities together, some of which need heuristics to select the  
>>>>> data.
>>>>>
>>>>> It should be relatively simple: Create a method that wraps a  
>>>>> call to findListIteratorByCondition, then grab the desired  
>>>>> range of results.  It should also return the size of the table.
>>>>>
>>>>> Then, as an example, we can call these methods with our  
>>>>> viewSize and viewIndex parameters, build our complex list of  
>>>>> data based on the results, and use the form-widget's override-
>>>>> list-size to make pagination work with it.
>>>>>
>>>>> Thoughts?
>>>>>
>>>>> - Leon
>


 

[BJ Freeman]

just as a side note
http://docs.ofbiz.org/display/~jacopoc/OFBiz+and+Oracle

if you notice in the framework/entity/config/entityengine.xml for oracle
    <datasource name="localoracle"
            helper-class="org.ofbiz.entity.datasource.GenericHelperDAO"
and the underlying GenericDAO.java
is where you would address these concerns.



phantom.coding sent the following on 8/28/2008 6:02 AM:

> Agree that EntityListIterator keeps database cursor open and uses that to
> retrive the results. Which means that we should use a scrollable cursor
> (correct me if I'm wrong!!). But when working with ORACLE this doesn't work.
> Oracle does not support scrollable cursors. It's the JDBC layer that
> simulates the scrollable cursor and not the DB server. So even if we fetch
> results 100 by 100 at JDBC level the full result set is fetched.
>
> Pls correct me if i'm wrong
>
>
>
>
> David E. Jones-2 wrote:
>>
>> This is incorrect. The EntityListIterator uses a database cursor and  
>> keeps the connection open to the database. Depending on what the  
>> database and JDBC driver support and how things are configured, it  
>> will typically pull over 100 records at a time over the network as it  
>> scrolls through or jumps around the result set.
>>
>> As to your specific performance problems, with that information I  
>> have no idea what the problem might be. It depends on how you have  
>> deployed OFBiz, how the database and JDBC driver are setup, and if  
>> the custom or OOTB OFBiz code is written properly to use the  
>> EntityListIterator.
>>
>> -David
>>
>>
>> On Jan 31, 2007, at 4:53 PM, Jonathon -- Improov wrote:
>>
>>> I could be wrong, but it seems OFBiz pulls down all records (1000s  
>>> possibly) before putting all those records through that function.  
>>> Yeah, I know, the partial list is gleaned off of the resultset and  
>>> so it seems that we're not exactly swallowing all records first.
>>>
>>> But isn't it more database-independent (or less?) to use SQL LIMIT?
>>>
>>> I'm seeing long load times for LookUpProduct service when listing  
>>> for pagination a mere 1000 records. Perhaps it'll be much much  
>>> faster using SQL LIMIT? I know for a fact that using SQL LIMIT is  
>>> faster than scrolling through a resultset, know that based on my  
>>> own apps (currently having entity framework that provides for SQL  
>>> LIMIT).
>>>
>>> Jonathon
>>>
>>> David E. Jones wrote:
>>>> Could you explain how that would be different and better than  
>>>> using the EntityListIterator.getPartialList method?
>>>> -David
>>>> On Jan 31, 2007, at 11:21 AM, Leon Torres wrote:
>>>>> Hi David,
>>>>>
>>>>> Sorry, maybe I should have expressed this in the form of two  
>>>>> questions.
>>>>>
>>>>> Is there a way to do a query with OFFSET and LIMIT using  
>>>>> EntityCondition?
>>>>>
>>>>> If not, then is there a way to do these offset and limit  
>>>>> operations with findEntityListIteratorByCondition?
>>>>>
>>>>> - Leon
>>>>>
>>>>>
>>>>>
>>>>> David E. Jones wrote:
>>>>>> What's wrong with the stuff that's been there for years on the  
>>>>>> EntityListIterator?
>>>>>> -David
>>>>>> On Jan 30, 2007, at 4:52 PM, Leon Torres wrote:
>>>>>>> Hi folks,
>>>>>>>
>>>>>>> I think we really need to be able to specify the size of the  
>>>>>>> list we want and the index to start at for the  
>>>>>>> GenericDelegator.findByAnd and findByCondition methods.
>>>>>>>
>>>>>>> The idea is to support pagination in the form widgets and  
>>>>>>> similar systems for lists of data that cannot be supported by  
>>>>>>> <view-entity>.  For example, if the inventory QOH and ATP are  
>>>>>>> required for a form-widget list, we need to call the  
>>>>>>> getInventoryAvailableByFacility service and add the results to  
>>>>>>> each list row. Another example would be a union of various  
>>>>>>> entities together, some of which need heuristics to select the  
>>>>>>> data.
>>>>>>>
>>>>>>> It should be relatively simple: Create a method that wraps a  
>>>>>>> call to findListIteratorByCondition, then grab the desired  
>>>>>>> range of results.  It should also return the size of the table.
>>>>>>>
>>>>>>> Then, as an example, we can call these methods with our  
>>>>>>> viewSize and viewIndex parameters, build our complex list of  
>>>>>>> data based on the results, and use the form-widget's override-
>>>>>>> list-size to make pagination work with it.
>>>>>>>
>>>>>>> Thoughts?
>>>>>>>
>>>>>>> - Leon
>>
>>  
>>
>

[phantom.coding]

My point is that oracle does not support scrollable cursors at the DB server. When we use a scrollable cursor with ORACLE it's the JDBC code that makes it look like we are fetching only 100 of records out of a large no of possible records. The query that executes against the DB actually fetches the entire set of records and not 100. It's nothing to do with the GenericDAO just the behaviour of ORACLE. So if the DB is ORACLE even if we use a EntityListIterator it doesn't do proper pagination

BJ Freeman wrote

just as a side note
http://docs.ofbiz.org/display/~jacopoc/OFBiz+and+Oracle

if you notice in the framework/entity/config/entityengine.xml for oracle
    <datasource name="localoracle"
            helper-class="org.ofbiz.entity.datasource.GenericHelperDAO"
and the underlying GenericDAO.java
is where you would address these concerns.



phantom.coding sent the following on 8/28/2008 6:02 AM:
> Agree that EntityListIterator keeps database cursor open and uses that to
> retrive the results. Which means that we should use a scrollable cursor
> (correct me if I'm wrong!!). But when working with ORACLE this doesn't work.
> Oracle does not support scrollable cursors. It's the JDBC layer that
> simulates the scrollable cursor and not the DB server. So even if we fetch
> results 100 by 100 at JDBC level the full result set is fetched.
>
> Pls correct me if i'm wrong
>
>
>
>
> David E. Jones-2 wrote:
>>
>> This is incorrect. The EntityListIterator uses a database cursor and  
>> keeps the connection open to the database. Depending on what the  
>> database and JDBC driver support and how things are configured, it  
>> will typically pull over 100 records at a time over the network as it  
>> scrolls through or jumps around the result set.
>>
>> As to your specific performance problems, with that information I  
>> have no idea what the problem might be. It depends on how you have  
>> deployed OFBiz, how the database and JDBC driver are setup, and if  
>> the custom or OOTB OFBiz code is written properly to use the  
>> EntityListIterator.
>>
>> -David
>>
>>
>> On Jan 31, 2007, at 4:53 PM, Jonathon -- Improov wrote:
>>
>>> I could be wrong, but it seems OFBiz pulls down all records (1000s  
>>> possibly) before putting all those records through that function.  
>>> Yeah, I know, the partial list is gleaned off of the resultset and  
>>> so it seems that we're not exactly swallowing all records first.
>>>
>>> But isn't it more database-independent (or less?) to use SQL LIMIT?
>>>
>>> I'm seeing long load times for LookUpProduct service when listing  
>>> for pagination a mere 1000 records. Perhaps it'll be much much  
>>> faster using SQL LIMIT? I know for a fact that using SQL LIMIT is  
>>> faster than scrolling through a resultset, know that based on my  
>>> own apps (currently having entity framework that provides for SQL  
>>> LIMIT).
>>>
>>> Jonathon
>>>
>>> David E. Jones wrote:
>>>> Could you explain how that would be different and better than  
>>>> using the EntityListIterator.getPartialList method?
>>>> -David
>>>> On Jan 31, 2007, at 11:21 AM, Leon Torres wrote:
>>>>> Hi David,
>>>>>
>>>>> Sorry, maybe I should have expressed this in the form of two  
>>>>> questions.
>>>>>
>>>>> Is there a way to do a query with OFFSET and LIMIT using  
>>>>> EntityCondition?
>>>>>
>>>>> If not, then is there a way to do these offset and limit  
>>>>> operations with findEntityListIteratorByCondition?
>>>>>
>>>>> - Leon
>>>>>
>>>>>
>>>>>
>>>>> David E. Jones wrote:
>>>>>> What's wrong with the stuff that's been there for years on the  
>>>>>> EntityListIterator?
>>>>>> -David
>>>>>> On Jan 30, 2007, at 4:52 PM, Leon Torres wrote:
>>>>>>> Hi folks,
>>>>>>>
>>>>>>> I think we really need to be able to specify the size of the  
>>>>>>> list we want and the index to start at for the  
>>>>>>> GenericDelegator.findByAnd and findByCondition methods.
>>>>>>>
>>>>>>> The idea is to support pagination in the form widgets and  
>>>>>>> similar systems for lists of data that cannot be supported by  
>>>>>>> <view-entity>.  For example, if the inventory QOH and ATP are  
>>>>>>> required for a form-widget list, we need to call the  
>>>>>>> getInventoryAvailableByFacility service and add the results to  
>>>>>>> each list row. Another example would be a union of various  
>>>>>>> entities together, some of which need heuristics to select the  
>>>>>>> data.
>>>>>>>
>>>>>>> It should be relatively simple: Create a method that wraps a  
>>>>>>> call to findListIteratorByCondition, then grab the desired  
>>>>>>> range of results.  It should also return the size of the table.
>>>>>>>
>>>>>>> Then, as an example, we can call these methods with our  
>>>>>>> viewSize and viewIndex parameters, build our complex list of  
>>>>>>> data based on the results, and use the form-widget's override-
>>>>>>> list-size to make pagination work with it.
>>>>>>>
>>>>>>> Thoughts?
>>>>>>>
>>>>>>> - Leon
>>
>>  
>>
>

[phantom.coding]

Refer this article

http://download-west.oracle.com/docs/cd/B10501_01/java.920/a96654/resltset.htm#1017914

So if we are to do correct pagination in OFBiz with ORACLE custom code must be used with ronum or something

phantom.coding wrote

My point is that oracle does not support scrollable cursors at the DB server. When we use a scrollable cursor with ORACLE it's the JDBC code that makes it look like we are fetching only 100 of records out of a large no of possible records. The query that executes against the DB actually fetches the entire set of records and not 100. It's nothing to do with the GenericDAO just the behaviour of ORACLE. So if the DB is ORACLE even if we use a EntityListIterator it doesn't do proper pagination

BJ Freeman wrote

just as a side note
http://docs.ofbiz.org/display/~jacopoc/OFBiz+and+Oracle

if you notice in the framework/entity/config/entityengine.xml for oracle
    <datasource name="localoracle"
            helper-class="org.ofbiz.entity.datasource.GenericHelperDAO"
and the underlying GenericDAO.java
is where you would address these concerns.



phantom.coding sent the following on 8/28/2008 6:02 AM:
> Agree that EntityListIterator keeps database cursor open and uses that to
> retrive the results. Which means that we should use a scrollable cursor
> (correct me if I'm wrong!!). But when working with ORACLE this doesn't work.
> Oracle does not support scrollable cursors. It's the JDBC layer that
> simulates the scrollable cursor and not the DB server. So even if we fetch
> results 100 by 100 at JDBC level the full result set is fetched.
>
> Pls correct me if i'm wrong
>
>
>
>
> David E. Jones-2 wrote:
>>
>> This is incorrect. The EntityListIterator uses a database cursor and  
>> keeps the connection open to the database. Depending on what the  
>> database and JDBC driver support and how things are configured, it  
>> will typically pull over 100 records at a time over the network as it  
>> scrolls through or jumps around the result set.
>>
>> As to your specific performance problems, with that information I  
>> have no idea what the problem might be. It depends on how you have  
>> deployed OFBiz, how the database and JDBC driver are setup, and if  
>> the custom or OOTB OFBiz code is written properly to use the  
>> EntityListIterator.
>>
>> -David
>>
>>
>> On Jan 31, 2007, at 4:53 PM, Jonathon -- Improov wrote:
>>
>>> I could be wrong, but it seems OFBiz pulls down all records (1000s  
>>> possibly) before putting all those records through that function.  
>>> Yeah, I know, the partial list is gleaned off of the resultset and  
>>> so it seems that we're not exactly swallowing all records first.
>>>
>>> But isn't it more database-independent (or less?) to use SQL LIMIT?
>>>
>>> I'm seeing long load times for LookUpProduct service when listing  
>>> for pagination a mere 1000 records. Perhaps it'll be much much  
>>> faster using SQL LIMIT? I know for a fact that using SQL LIMIT is  
>>> faster than scrolling through a resultset, know that based on my  
>>> own apps (currently having entity framework that provides for SQL  
>>> LIMIT).
>>>
>>> Jonathon
>>>
>>> David E. Jones wrote:
>>>> Could you explain how that would be different and better than  
>>>> using the EntityListIterator.getPartialList method?
>>>> -David
>>>> On Jan 31, 2007, at 11:21 AM, Leon Torres wrote:
>>>>> Hi David,
>>>>>
>>>>> Sorry, maybe I should have expressed this in the form of two  
>>>>> questions.
>>>>>
>>>>> Is there a way to do a query with OFFSET and LIMIT using  
>>>>> EntityCondition?
>>>>>
>>>>> If not, then is there a way to do these offset and limit  
>>>>> operations with findEntityListIteratorByCondition?
>>>>>
>>>>> - Leon
>>>>>
>>>>>
>>>>>
>>>>> David E. Jones wrote:
>>>>>> What's wrong with the stuff that's been there for years on the  
>>>>>> EntityListIterator?
>>>>>> -David
>>>>>> On Jan 30, 2007, at 4:52 PM, Leon Torres wrote:
>>>>>>> Hi folks,
>>>>>>>
>>>>>>> I think we really need to be able to specify the size of the  
>>>>>>> list we want and the index to start at for the  
>>>>>>> GenericDelegator.findByAnd and findByCondition methods.
>>>>>>>
>>>>>>> The idea is to support pagination in the form widgets and  
>>>>>>> similar systems for lists of data that cannot be supported by  
>>>>>>> <view-entity>.  For example, if the inventory QOH and ATP are  
>>>>>>> required for a form-widget list, we need to call the  
>>>>>>> getInventoryAvailableByFacility service and add the results to  
>>>>>>> each list row. Another example would be a union of various  
>>>>>>> entities together, some of which need heuristics to select the  
>>>>>>> data.
>>>>>>>
>>>>>>> It should be relatively simple: Create a method that wraps a  
>>>>>>> call to findListIteratorByCondition, then grab the desired  
>>>>>>> range of results.  It should also return the size of the table.
>>>>>>>
>>>>>>> Then, as an example, we can call these methods with our  
>>>>>>> viewSize and viewIndex parameters, build our complex list of  
>>>>>>> data based on the results, and use the form-widget's override-
>>>>>>> list-size to make pagination work with it.
>>>>>>>
>>>>>>> Thoughts?
>>>>>>>
>>>>>>> - Leon
>>
>>  
>>
>

[David E Jones]

We usually want ResultSet.TYPE_SCROLL_INSENSITIVE, which should work properly on the database side.

We do NOT want ResultSet.TYPE_SCROLL_SENSITIVE because it is sensitive to changes and has more performance impact.

Still, I'm guessing the information you are referring to is very old and I would be astounded if the Oracle JDBC driver
still had this issue, in fact I'm fairly sure it does not.

-David


phantom.coding wrote:

> Agree that EntityListIterator keeps database cursor open and uses that to
> retrive the results. Which means that we should use a scrollable cursor
> (correct me if I'm wrong!!). But when working with ORACLE this doesn't work.
> Oracle does not support scrollable cursors. It's the JDBC layer that
> simulates the scrollable cursor and not the DB server. So even if we fetch
> results 100 by 100 at JDBC level the full result set is fetched.
>
> Pls correct me if i'm wrong
>
>
>
>
> David E. Jones-2 wrote:
>>
>> This is incorrect. The EntityListIterator uses a database cursor and  
>> keeps the connection open to the database. Depending on what the  
>> database and JDBC driver support and how things are configured, it  
>> will typically pull over 100 records at a time over the network as it  
>> scrolls through or jumps around the result set.
>>
>> As to your specific performance problems, with that information I  
>> have no idea what the problem might be. It depends on how you have  
>> deployed OFBiz, how the database and JDBC driver are setup, and if  
>> the custom or OOTB OFBiz code is written properly to use the  
>> EntityListIterator.
>>
>> -David
>>
>>
>> On Jan 31, 2007, at 4:53 PM, Jonathon -- Improov wrote:
>>
>>> I could be wrong, but it seems OFBiz pulls down all records (1000s  
>>> possibly) before putting all those records through that function.  
>>> Yeah, I know, the partial list is gleaned off of the resultset and  
>>> so it seems that we're not exactly swallowing all records first.
>>>
>>> But isn't it more database-independent (or less?) to use SQL LIMIT?
>>>
>>> I'm seeing long load times for LookUpProduct service when listing  
>>> for pagination a mere 1000 records. Perhaps it'll be much much  
>>> faster using SQL LIMIT? I know for a fact that using SQL LIMIT is  
>>> faster than scrolling through a resultset, know that based on my  
>>> own apps (currently having entity framework that provides for SQL  
>>> LIMIT).
>>>
>>> Jonathon
>>>
>>> David E. Jones wrote:
>>>> Could you explain how that would be different and better than  
>>>> using the EntityListIterator.getPartialList method?
>>>> -David
>>>> On Jan 31, 2007, at 11:21 AM, Leon Torres wrote:
>>>>> Hi David,
>>>>>
>>>>> Sorry, maybe I should have expressed this in the form of two  
>>>>> questions.
>>>>>
>>>>> Is there a way to do a query with OFFSET and LIMIT using  
>>>>> EntityCondition?
>>>>>
>>>>> If not, then is there a way to do these offset and limit  
>>>>> operations with findEntityListIteratorByCondition?
>>>>>
>>>>> - Leon
>>>>>
>>>>>
>>>>>
>>>>> David E. Jones wrote:
>>>>>> What's wrong with the stuff that's been there for years on the  
>>>>>> EntityListIterator?
>>>>>> -David
>>>>>> On Jan 30, 2007, at 4:52 PM, Leon Torres wrote:
>>>>>>> Hi folks,
>>>>>>>
>>>>>>> I think we really need to be able to specify the size of the  
>>>>>>> list we want and the index to start at for the  
>>>>>>> GenericDelegator.findByAnd and findByCondition methods.
>>>>>>>
>>>>>>> The idea is to support pagination in the form widgets and  
>>>>>>> similar systems for lists of data that cannot be supported by  
>>>>>>> <view-entity>.  For example, if the inventory QOH and ATP are  
>>>>>>> required for a form-widget list, we need to call the  
>>>>>>> getInventoryAvailableByFacility service and add the results to  
>>>>>>> each list row. Another example would be a union of various  
>>>>>>> entities together, some of which need heuristics to select the  
>>>>>>> data.
>>>>>>>
>>>>>>> It should be relatively simple: Create a method that wraps a  
>>>>>>> call to findListIteratorByCondition, then grab the desired  
>>>>>>> range of results.  It should also return the size of the table.
>>>>>>>
>>>>>>> Then, as an example, we can call these methods with our  
>>>>>>> viewSize and viewIndex parameters, build our complex list of  
>>>>>>> data based on the results, and use the form-widget's override-
>>>>>>> list-size to make pagination work with it.
>>>>>>>
>>>>>>> Thoughts?
>>>>>>>
>>>>>>> - Leon
>>
>>  
>>
>

[phantom.coding]

I think this is the case with ORACLE 9i for sure. Not certain about 10g. Hibernate also confirms this

http://www.hibernate.org/314.html

David E. Jones-2 wrote

We usually want ResultSet.TYPE_SCROLL_INSENSITIVE, which should work properly on the database side.

We do NOT want ResultSet.TYPE_SCROLL_SENSITIVE because it is sensitive to changes and has more performance impact.

Still, I'm guessing the information you are referring to is very old and I would be astounded if the Oracle JDBC driver
still had this issue, in fact I'm fairly sure it does not.

-David


phantom.coding wrote:
> Agree that EntityListIterator keeps database cursor open and uses that to
> retrive the results. Which means that we should use a scrollable cursor
> (correct me if I'm wrong!!). But when working with ORACLE this doesn't work.
> Oracle does not support scrollable cursors. It's the JDBC layer that
> simulates the scrollable cursor and not the DB server. So even if we fetch
> results 100 by 100 at JDBC level the full result set is fetched.
>
> Pls correct me if i'm wrong
>
>
>
>
> David E. Jones-2 wrote:
>>
>> This is incorrect. The EntityListIterator uses a database cursor and  
>> keeps the connection open to the database. Depending on what the  
>> database and JDBC driver support and how things are configured, it  
>> will typically pull over 100 records at a time over the network as it  
>> scrolls through or jumps around the result set.
>>
>> As to your specific performance problems, with that information I  
>> have no idea what the problem might be. It depends on how you have  
>> deployed OFBiz, how the database and JDBC driver are setup, and if  
>> the custom or OOTB OFBiz code is written properly to use the  
>> EntityListIterator.
>>
>> -David
>>
>>
>> On Jan 31, 2007, at 4:53 PM, Jonathon -- Improov wrote:
>>
>>> I could be wrong, but it seems OFBiz pulls down all records (1000s  
>>> possibly) before putting all those records through that function.  
>>> Yeah, I know, the partial list is gleaned off of the resultset and  
>>> so it seems that we're not exactly swallowing all records first.
>>>
>>> But isn't it more database-independent (or less?) to use SQL LIMIT?
>>>
>>> I'm seeing long load times for LookUpProduct service when listing  
>>> for pagination a mere 1000 records. Perhaps it'll be much much  
>>> faster using SQL LIMIT? I know for a fact that using SQL LIMIT is  
>>> faster than scrolling through a resultset, know that based on my  
>>> own apps (currently having entity framework that provides for SQL  
>>> LIMIT).
>>>
>>> Jonathon
>>>
>>> David E. Jones wrote:
>>>> Could you explain how that would be different and better than  
>>>> using the EntityListIterator.getPartialList method?
>>>> -David
>>>> On Jan 31, 2007, at 11:21 AM, Leon Torres wrote:
>>>>> Hi David,
>>>>>
>>>>> Sorry, maybe I should have expressed this in the form of two  
>>>>> questions.
>>>>>
>>>>> Is there a way to do a query with OFFSET and LIMIT using  
>>>>> EntityCondition?
>>>>>
>>>>> If not, then is there a way to do these offset and limit  
>>>>> operations with findEntityListIteratorByCondition?
>>>>>
>>>>> - Leon
>>>>>
>>>>>
>>>>>
>>>>> David E. Jones wrote:
>>>>>> What's wrong with the stuff that's been there for years on the  
>>>>>> EntityListIterator?
>>>>>> -David
>>>>>> On Jan 30, 2007, at 4:52 PM, Leon Torres wrote:
>>>>>>> Hi folks,
>>>>>>>
>>>>>>> I think we really need to be able to specify the size of the  
>>>>>>> list we want and the index to start at for the  
>>>>>>> GenericDelegator.findByAnd and findByCondition methods.
>>>>>>>
>>>>>>> The idea is to support pagination in the form widgets and  
>>>>>>> similar systems for lists of data that cannot be supported by  
>>>>>>> <view-entity>.  For example, if the inventory QOH and ATP are  
>>>>>>> required for a form-widget list, we need to call the  
>>>>>>> getInventoryAvailableByFacility service and add the results to  
>>>>>>> each list row. Another example would be a union of various  
>>>>>>> entities together, some of which need heuristics to select the  
>>>>>>> data.
>>>>>>>
>>>>>>> It should be relatively simple: Create a method that wraps a  
>>>>>>> call to findListIteratorByCondition, then grab the desired  
>>>>>>> range of results.  It should also return the size of the table.
>>>>>>>
>>>>>>> Then, as an example, we can call these methods with our  
>>>>>>> viewSize and viewIndex parameters, build our complex list of  
>>>>>>> data based on the results, and use the form-widget's override-
>>>>>>> list-size to make pagination work with it.
>>>>>>>
>>>>>>> Thoughts?
>>>>>>>
>>>>>>> - Leon
>>
>>  
>>
>

[BJ Freeman]

I undestood your concern and pointed you to where you can customize your
entity list so conform with calls to your jdbc, in the GenericHelperDAO.
so you would have orcale_GenericHelperDAO.
unless you suggesting a full refactor of ofbiz code to accommodate your DB.

phantom.coding sent the following on 8/28/2008 11:50 PM:

> My point is that oracle does not support scrollable cursors at the DB server.
> When we use a scrollable cursor with ORACLE it's the JDBC code that makes it
> look like we are fetching only 100 of records out of a large no of possible
> records. The query that executes against the DB actually fetches the entire
> set of records and not 100. It's nothing to do with the GenericDAO just the
> behaviour of ORACLE. So if the DB is ORACLE even if we use a
> EntityListIterator it doesn't do proper pagination
>
>
>
> BJ Freeman wrote:
>> just as a side note
>> http://docs.ofbiz.org/display/~jacopoc/OFBiz+and+Oracle
>>
>> if you notice in the framework/entity/config/entityengine.xml for oracle
>>     <datasource name="localoracle"
>>             helper-class="org.ofbiz.entity.datasource.GenericHelperDAO"
>> and the underlying GenericDAO.java
>> is where you would address these concerns.
>>
>>
>>
>> phantom.coding sent the following on 8/28/2008 6:02 AM:
>>> Agree that EntityListIterator keeps database cursor open and uses that to
>>> retrive the results. Which means that we should use a scrollable cursor
>>> (correct me if I'm wrong!!). But when working with ORACLE this doesn't
>>> work.
>>> Oracle does not support scrollable cursors. It's the JDBC layer that
>>> simulates the scrollable cursor and not the DB server. So even if we
>>> fetch
>>> results 100 by 100 at JDBC level the full result set is fetched.
>>>
>>> Pls correct me if i'm wrong
>>>
>>>
>>>
>>>
>>> David E. Jones-2 wrote:
>>>> This is incorrect. The EntityListIterator uses a database cursor and  
>>>> keeps the connection open to the database. Depending on what the  
>>>> database and JDBC driver support and how things are configured, it  
>>>> will typically pull over 100 records at a time over the network as it  
>>>> scrolls through or jumps around the result set.
>>>>
>>>> As to your specific performance problems, with that information I  
>>>> have no idea what the problem might be. It depends on how you have  
>>>> deployed OFBiz, how the database and JDBC driver are setup, and if  
>>>> the custom or OOTB OFBiz code is written properly to use the  
>>>> EntityListIterator.
>>>>
>>>> -David
>>>>
>>>>
>>>> On Jan 31, 2007, at 4:53 PM, Jonathon -- Improov wrote:
>>>>
>>>>> I could be wrong, but it seems OFBiz pulls down all records (1000s  
>>>>> possibly) before putting all those records through that function.  
>>>>> Yeah, I know, the partial list is gleaned off of the resultset and  
>>>>> so it seems that we're not exactly swallowing all records first.
>>>>>
>>>>> But isn't it more database-independent (or less?) to use SQL LIMIT?
>>>>>
>>>>> I'm seeing long load times for LookUpProduct service when listing  
>>>>> for pagination a mere 1000 records. Perhaps it'll be much much  
>>>>> faster using SQL LIMIT? I know for a fact that using SQL LIMIT is  
>>>>> faster than scrolling through a resultset, know that based on my  
>>>>> own apps (currently having entity framework that provides for SQL  
>>>>> LIMIT).
>>>>>
>>>>> Jonathon
>>>>>
>>>>> David E. Jones wrote:
>>>>>> Could you explain how that would be different and better than  
>>>>>> using the EntityListIterator.getPartialList method?
>>>>>> -David
>>>>>> On Jan 31, 2007, at 11:21 AM, Leon Torres wrote:
>>>>>>> Hi David,
>>>>>>>
>>>>>>> Sorry, maybe I should have expressed this in the form of two  
>>>>>>> questions.
>>>>>>>
>>>>>>> Is there a way to do a query with OFFSET and LIMIT using  
>>>>>>> EntityCondition?
>>>>>>>
>>>>>>> If not, then is there a way to do these offset and limit  
>>>>>>> operations with findEntityListIteratorByCondition?
>>>>>>>
>>>>>>> - Leon
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> David E. Jones wrote:
>>>>>>>> What's wrong with the stuff that's been there for years on the  
>>>>>>>> EntityListIterator?
>>>>>>>> -David
>>>>>>>> On Jan 30, 2007, at 4:52 PM, Leon Torres wrote:
>>>>>>>>> Hi folks,
>>>>>>>>>
>>>>>>>>> I think we really need to be able to specify the size of the  
>>>>>>>>> list we want and the index to start at for the  
>>>>>>>>> GenericDelegator.findByAnd and findByCondition methods.
>>>>>>>>>
>>>>>>>>> The idea is to support pagination in the form widgets and  
>>>>>>>>> similar systems for lists of data that cannot be supported by  
>>>>>>>>> <view-entity>.  For example, if the inventory QOH and ATP are  
>>>>>>>>> required for a form-widget list, we need to call the  
>>>>>>>>> getInventoryAvailableByFacility service and add the results to  
>>>>>>>>> each list row. Another example would be a union of various  
>>>>>>>>> entities together, some of which need heuristics to select the  
>>>>>>>>> data.
>>>>>>>>>
>>>>>>>>> It should be relatively simple: Create a method that wraps a  
>>>>>>>>> call to findListIteratorByCondition, then grab the desired  
>>>>>>>>> range of results.  It should also return the size of the table.
>>>>>>>>>
>>>>>>>>> Then, as an example, we can call these methods with our  
>>>>>>>>> viewSize and viewIndex parameters, build our complex list of  
>>>>>>>>> data based on the results, and use the form-widget's override-
>>>>>>>>> list-size to make pagination work with it.
>>>>>>>>>
>>>>>>>>> Thoughts?
>>>>>>>>>
>>>>>>>>> - Leon
>>>>  
>>>>
>>
>>
>