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 >>>> >>>> >>> >>> >
