This is interesting, but you did not answer about the JDBC driver used. I'm far from a DB specialist but maybe you could read http://troels.arvin.dk/db/rdbms/ This document explains some aspects of CURSOR http://troels.arvin.dk/db/rdbms/#select-limit and LIMIT-OFFSET couple http://troels.arvin.dk/db/rdbms/#select-limit-offset.
Did you have a look at the use-iterator=true(minilang) <use-iterator>(widget) option (entityListIterator) ? This uses a database cursor for you. I suppose your lack of OFBiz researches is the reason David did not answer you, the cursor word should have make you aware ;o). Simply look for "use-iterator" in *.java,*.xml file in Eclipse... Jacques PS : I think I will make a FAQ, How to use cursor or LIMIT-OFFSET couple in OFBiz... ----- Original Message ----- From: ian tabangay To: [email protected] Sent: Wednesday, September 10, 2008 6:21 AM Subject: Re: Concerned about how ofbiz is doing paginate Here's the result of the benchmarking you've requested. Attached are the files used for this benchmark. Please comment if I did the testing the wrong way and how it should be done. Testing procedures: 1. Entity ProductFacility was used for both tests. ProductFacility has 21897 rows at the time of the testing. 2. Both test were deployed as a service which is being called thru 'Run Service' available under Webtools 3. Services are called alternately, starting with testLimitOffset, clearing cache after each execution of a service. 4. Each service has a viewSize of 100 and viewIndex of 217. 5. Note that org.ofbiz.entity.util.EntityFindOptions (of rev 496982) and org.ofbiz.entity.datasource.GenericDAO (of rev 686591) were changed to accommodate insertions of LIMIT and OFFSET into the sql builder. I added a field 'offset' and getter-setter for it for the class org.ofbiz.entity.util.EntityFindOptions. I inserted the following lines on line 724 for the class org.ofbiz.entity.datasource.GenericDAO. Modified classes are attached for your inspection. -- start code -- // LIMIT if (findOptions.getMaxRows() > 0) { sqlBuffer.append(" LIMIT " + findOptions.getMaxRows()); } // OFFSET if (findOptions.getOffset() > 0) { sqlBuffer.append(" OFFSET " + findOptions.getOffset()); } -- end code -- 6. The database being used is postgresql 8.1 7. Platform specs: Intel CoreDuo 1.8Ghz, 2gb Ram, Windows XP ver 2002 SP 2 Test Results are as follow: testResultSet: 797, 875, 750, 750, 765 (Ave: 787.4 ms) generated sql: SELECT PRODUCT_ID, FACILITY_ID, MINIMUM_STOCK, REORDER_QUANTITY, DAYS_TO_SHIP, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP, MAXIMUM_STOCK, ALLOCATION FROM public.PRODUCT_FACILITY testLimitOffset: 141, 125, 109, 93, 94 (Ave: 112.4 ms) generated sql: SELECT PRODUCT_ID, FACILITY_ID, MINIMUM_STOCK, REORDER_QUANTITY, DAYS_TO_SHIP, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP, MAXIMUM_STOCK, ALLOCATION FROM public.PRODUCT_FACILITY LIMIT 100 OFFSET 21700 Also, why not set the cursor size and what not (as is done, optional of course, in the entityengine.xml file)? Sorry I dont understand what you mean by this. Again, thank you very much for your time. ~ ian On Tue, Sep 9, 2008 at 9:17 PM, David E Jones <[EMAIL PROTECTED]> wrote: What are the database and JDBC driver versions you are working with? Also, the JDBC code you included does a few things differently than what the Entity Engine does. Have you done similar tests using the Entity Engine instead of plain JDBC? Also, why not set the cursor size and what not (as is done, optional of course, in the entityengine.xml file)? -David
