A bit of investigation shows that this is already implemented in selectCountByCondition.
Daniel On Sun, Jun 28, 2009 at 10:16 AM, Daniel Riquelme <[email protected] > wrote: > Hi, > > This code is in org.ofbiz.order.order.OrderListState. > A possible workaround would be to to do a select count(*). > Any more suggestions ? > > > On Sat, Jun 27, 2009 at 10:14 PM, David E Jones <[email protected]> wrote: > >> >> Is this in your custom code or in something that exists in OFBiz? If it is >> in OFBiz I'd like to change it... >> >> This problem has been around for a long time and is actually documented >> thoroughly in the EntityListIterator JavaDocs, but that doesn't mean all >> developers follow the recommendations there! >> >> -David >> >> >> >> On Jun 27, 2009, at 6:49 PM, Daniel Riquelme wrote: >> >> Hi, after a few hours of investigation and database tunning I figured it >>> out. >>> The queryhas been optimized to run in 40~70 seconds. >>> The ofbiz log displays a line like the following: >>> >>> Ran query in 64695 milli-seconds: SELECT ORDER_ID, ORDER_TYPE_ID, >>> ORDER_NAME, EXTERNAL_ID, SALES_CHANNEL_ENUM_ID, ORDER_DATE, ENTRY_DATE, >>> VISIT_ID, STATUS_ID, CREATED_BY, FIRST_ATTEMPT_ORDER_ID, CURRENCY_UOM, >>> SYNC_STATUS_ID, BILLING_ACCOUNT_ID, ORIGIN_FACILITY_ID, WEB_SITE_ID, >>> PRODUCT_STORE_ID, TERMINAL_ID, TRANSACTION_ID, >>> AUTO_ORDER_SHOPPING_LIST_ID, >>> NEEDS_INVENTORY_ISSUANCE, IS_RUSH_ORDER, INTERNAL_CODE, >>> REMAINING_SUB_TOTAL, >>> GRAND_TOTAL, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, >>> CREATED_TX_STAMP, RECIBIR_BODEGAL, RECEPCIONADA_BODEGAL, >>> FECHA_RECEPCION_BODEGAL FROM ERP.ORDER_HEADER WHERE ((STATUS_ID = ? OR >>> STATUS_ID = ? OR STATUS_ID = ? OR STATUS_ID = ? OR STATUS_ID = ? OR >>> STATUS_ID = ? OR STATUS_ID = ?) AND (ORDER_TYPE_ID = ?)) ORDER BY >>> ORDER_DATE >>> DESC >>> >>> After a very long wait an out of memory error occurs. >>> The VM has already been configured to use 3Gigs.- >>> >>> The method that actually never returns and consumes all available memory >>> is >>> EntityListIterator.last >>> Which handles the job to the jdbc driver ScrollableResultSet.last() >>> method >>> This methos caches every single line returned by the query. >>> >>> The Oracle documentation says the following about Scrollable Result Sets: >>> >>> "If the ResultSet is very large, resultset.last() may be a very >>> time-consuming operation, since it will use more resources on the server >>> side. So, unless you really need a scrollable ResultSet, refrain from >>> using >>> this approach. " >>> This part of the documentation talks about the possible techniques for >>> counting the total number of rows in a query. >>> >>> After commenting the code not to use the EntityListIterator.last() the >>> request displays in about the same time the query finishes. >>> >>> Thanks a lot for your help, >>> Daniel >>> >>> On Fri, Jun 26, 2009 at 10:11 AM, Daniel Riquelme < >>> [email protected] >>> >>>> wrote: >>>> >>> >>> When the distinct option is removed the query returns in less than a >>>> sec. >>>> So the problem must be database related. >>>> >>>> >>>> On Thu, Jun 25, 2009 at 8:30 PM, Daniel Riquelme < >>>> [email protected]> wrote: >>>> >>>> Hi, >>>>> >>>>> I have 4.000.000+ Sales Orders I need to migrate to ofbiz.- >>>>> I've already have set up an Oracle database with the data. >>>>> The problem is that when I click on the ordermgr link the page never >>>>> displays, the browser keeps waiting for ever. >>>>> Eventually, the transactions gets timed out. >>>>> >>>>> I've been investigating this problem and found that is not common. >>>>> >>>>> I'm using the oracle driver version 11.1 with default ofbiz >>>>> configuration. >>>>> The database is oracle 10g r2. >>>>> >>>>> I came to the conclusion that my database is not configured properly. >>>>> Nevertheles I would like to share this problem with you, perhaps there >>>>> is >>>>> a simple solution to it that I'am missing. >>>>> >>>>> I have already read the posts regarding fetch size configuration. I've >>>>> tested with fetch-size=50 and fetch-size=500 with no results. >>>>> >>>>> An ofbiz debug shows that the slow operation is in: >>>>> org.ofbiz.entity.datasource.selectListIteratorByCondition >>>>> >>>>> The call that never returns is: >>>>> sqlP.executeQuery(); >>>>> >>>>> The SQL query is (as reported by oracle): >>>>> SELECT DISTINCT ORDER_ID, ORDER_TYPE_ID, ORDER_NAME, EXTERNAL_ID, >>>>> SALES_CHANNEL_ENUM_ID, ORDER_DATE, ENTRY_DATE, VISIT_ID, STATUS_ID, >>>>> CREATED_BY, FIRST_ATTEMPT_ORDER_ID, CURRENCY_UOM, SYNC_STATUS_ID, >>>>> BILLING_ACCOUNT_ID, ORIGIN_FACILITY_ID, WEB_SITE_ID, PRODUCT_STORE_ID, >>>>> TERMINAL_ID, TRANSACTION_ID, AUTO_ORDER_SHOPPING_LIST_ID, >>>>> NEEDS_INVENTORY_ISSUANCE, IS_RUSH_ORDER, INTERNAL_CODE, >>>>> REMAINING_SUB_TOTAL, >>>>> GRAND_TOTAL, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, >>>>> CREATED_TX_STAMP, RECIBIR_BODEGAL, RECEPCIONADA_BODEGAL, >>>>> FECHA_RECEPCION_BODEGAL FROM ERP.ORDER_HEADER WHERE ((STATUS_ID = :v0 >>>>> OR >>>>> STATUS_ID = :v1 OR STATUS_ID = :v2) AND (ORDER_TYPE_ID = :v3)) ORDER BY >>>>> ORDER_DATE DESC >>>>> >>>>> Oracle reports that the session is waiting on a: >>>>> db file scattered read >>>>> >>>>> >>>>> I'm not an expert on this subject. >>>>> Any one has a tip ? >>>>> >>>>> Thanks, >>>>> Daniel >>>>> >>>>> >>>> >>>> >> >
