You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ofbiz.apache.org by "phantom.coding" <ph...@gmail.com> on 2008/08/28 15:02:37 UTC
Re: Idea: be able to specify size and index of entity lists
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
>>
>
>
>
>
--
View this message in context: http://www.nabble.com/Idea%3A--be-able-to-specify-size-and-index-of-entity-lists-tp8720621p19200062.html
Sent from the OFBiz - Dev mailing list archive at Nabble.com.
Re: Idea: be able to specify size and index of entity lists
Posted by BJ Freeman <bj...@free-man.net>.
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
>>>>
>>>>
>>
>>
>
Re: Idea: be able to specify size and index of entity lists
Posted by "phantom.coding" <ph...@gmail.com>.
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
>>>>
>>>>
>>>>
>>>
>>
>>
>>
>
>
--
View this message in context: http://www.nabble.com/Idea%3A--be-able-to-specify-size-and-index-of-entity-lists-tp8720621p19214684.html
Sent from the OFBiz - Dev mailing list archive at Nabble.com.
Re: Idea: be able to specify size and index of entity lists
Posted by "phantom.coding" <ph...@gmail.com>.
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
>>>
>>>
>>>
>>
>
>
>
--
View this message in context: http://www.nabble.com/Idea%3A--be-able-to-specify-size-and-index-of-entity-lists-tp8720621p19214628.html
Sent from the OFBiz - Dev mailing list archive at Nabble.com.
Re: Idea: be able to specify size and index of entity lists
Posted by BJ Freeman <bj...@free-man.net>.
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
>>
>>
>>
>
Re: Idea: be able to specify size and index of entity lists
Posted by "phantom.coding" <ph...@gmail.com>.
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
>>>
>>>
>>>
>>
>
>
--
View this message in context: http://www.nabble.com/Idea%3A--be-able-to-specify-size-and-index-of-entity-lists-tp8720621p19217222.html
Sent from the OFBiz - Dev mailing list archive at Nabble.com.
Re: Idea: be able to specify size and index of entity lists
Posted by "David E. Jones" <jo...@hotwaxmedia.com>.
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
>>
>>
>>
>