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.
