Re: Slow EntityListIterator

Sun, 28 Jun 2009 09:05:29 -0700
Interesting... looks like you're referring to line number 255 in OrderListState.java, is that correct? 


When I responded before I was incorrect, thinking of the "previous"  
method instead of the "last" method. If the "last" method really  
causes the JDBC driver to iterate of EVERY record then that is a REAL  
bummer! Most databases and JDBCs drivers will do a simple operation  
and jump to the last result instead of iterating through each one.



This is used in other places in OFBiz to determine the result size  
without doing an additional query (which may also result in a  
different number because other things may have happened between the  
two queries). BTW, for those reading in: there is a convenience method  
in the ELI for doing this: getResultsSizeAfterPartialList().



We could change these to use a selectCount* instead of using  
EntityListIterator.last or .getResultsSizeAfterPartialList(), but I  
wonder if that would actually be slower on some databases.


Has anyone played around with this more?


Daniel: have you tried Oracle with any other JDBC drivers? If you're  
using the ones from Oracle I know historically they have had a number  
of issues and 3rd part drivers usually result in FAR better  
performance and resource utilization.


-David


On Jun 28, 2009, at 8:16 AM, Daniel Riquelme 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































					Reply via email to