Slow EntityListIterator

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

Slow EntityListIterator

Daniel Riquelme
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 | Threaded
Open this post in threaded view
|

Re: Slow EntityListIterator

Daniel Riquelme
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
<[hidden email]>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 | Threaded
Open this post in threaded view
|

Re: Slow EntityListIterator

Daniel Riquelme
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 <[hidden email]
> 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 <
> [hidden email]> 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 | Threaded
Open this post in threaded view
|

Re: Slow EntityListIterator

BJ Freeman
In reply to this post by Daniel Riquelme
this did not mention anything about this issue but for reference
http://docs.ofbiz.org/display/~jacopoc/OFBiz+and+Oracle

then type in the search box
oracle I see some others but did not review them.
I would suggest you take that query an run it external to ofbiz
not familiar with oracle tools to suggest how.
see if you get the same results.
may be an index problem.
Maybe cache of the results using memory.

I remember some thing vague about cursors an oracle

cursors allow only a segment

the last command may send a query for the whole result set.
there may be a workaround for this but I think you will find it in the
oracle group not in ofbiz.




Daniel Riquelme sent the following on 6/27/2009 5:49 PM:

> 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 <[hidden email]
>> 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 <
>> [hidden email]> 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
>>>
>>
>

--
BJ Freeman
http://www.businessesnetwork.com/automation
http://bjfreeman.elance.com
http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro
Systems Integrator.

Reply | Threaded
Open this post in threaded view
|

Re: Slow EntityListIterator

David E. Jones-2
In reply to this post by Daniel Riquelme

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 <[hidden email]
>> 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 <
>> [hidden email]> 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 | Threaded
Open this post in threaded view
|

Re: Slow EntityListIterator

Daniel Riquelme
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 <[hidden email]> 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 <
>> [hidden email]
>>
>>> 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 <
>>> [hidden email]> 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 | Threaded
Open this post in threaded view
|

Re: Slow EntityListIterator

Daniel Riquelme
A bit of investigation shows that this is already implemented in
selectCountByCondition.

Daniel

On Sun, Jun 28, 2009 at 10:16 AM, Daniel Riquelme <[hidden email]
> 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 <[hidden email]> 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 <
>>> [hidden email]
>>>
>>>> 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 <
>>>> [hidden email]> 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 | Threaded
Open this post in threaded view
|

Re: Slow EntityListIterator

David E. Jones-2
In reply to this post by Daniel Riquelme

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 <[hidden email]> 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 <
>>> [hidden email]
>>>
>>>> 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 <
>>>> [hidden email]> 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 | Threaded
Open this post in threaded view
|

Re: Slow EntityListIterator

Daniel Riquelme
Hi David, I have tested DataDirect driver with the same slow result.
I must be missing something.
I have checked that caching of every row occurs by debugging. Obviously, I
don't have the code to any of the jdbc drivers in question but I'm still
able to see what methods are been called and it's pretty obvious that the
driver is traversing and caching every single row.
Maybe there is a configuration that disables this behaviour ?

Daniel

On Sun, Jun 28, 2009 at 12:04 PM, David E Jones <[hidden email]> wrote:

>
> 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 <[hidden email]> 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 <
>>>> [hidden email]
>>>>
>>>>  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 <
>>>>> [hidden email]> 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 | Threaded
Open this post in threaded view
|

Re: Slow EntityListIterator

BJ Freeman
In reply to this post by David E. Jones-2
not sure if this is going to help. I am not an oracle person.
but since this does not happen on other DB, I did a search on google for
oracle get last
one solution i found used a Select max(fieldname)
here is the thread
http://www.daniweb.com/forums/thread93986.html#

I would suggest you ask on Oracle support.


Daniel Riquelme sent the following on 6/29/2009 9:05 AM:

> Hi David, I have tested DataDirect driver with the same slow result.
> I must be missing something.
> I have checked that caching of every row occurs by debugging. Obviously, I
> don't have the code to any of the jdbc drivers in question but I'm still
> able to see what methods are been called and it's pretty obvious that the
> driver is traversing and caching every single row.
> Maybe there is a configuration that disables this behaviour ?
>
> Daniel
>
> On Sun, Jun 28, 2009 at 12:04 PM, David E Jones <[hidden email]> wrote:
>
>> 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 <[hidden email]> 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 <
>>>>> [hidden email]
>>>>>
>>>>>  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 <
>>>>>> [hidden email]> 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
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>

--
BJ Freeman
http://www.businessesnetwork.com/automation
http://bjfreeman.elance.com
http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro
Systems Integrator.

Reply | Threaded
Open this post in threaded view
|

Re: Slow EntityListIterator

Daniel Riquelme
Thanks BJ I'll take a look at that link.
Nevertheless I've been thinking about this possible problem with oracle and
I became concerned with the fact that oracle is one of the most used
database engines in production today.
I'm aware that no one else is reporting this issue so I'm confident that the
problem must be configuration related.
I will test this with postgres to see what happens.
The MySQL driver has the same slow behaviour.

Thanks a lot for the commenst so far

On Mon, Jun 29, 2009 at 1:23 PM, BJ Freeman <[hidden email]> wrote:

> not sure if this is going to help. I am not an oracle person.
> but since this does not happen on other DB, I did a search on google for
> oracle get last
> one solution i found used a Select max(fieldname)
> here is the thread
> http://www.daniweb.com/forums/thread93986.html#
>
> I would suggest you ask on Oracle support.
>
>
> Daniel Riquelme sent the following on 6/29/2009 9:05 AM:
> > Hi David, I have tested DataDirect driver with the same slow result.
> > I must be missing something.
> > I have checked that caching of every row occurs by debugging. Obviously,
> I
> > don't have the code to any of the jdbc drivers in question but I'm still
> > able to see what methods are been called and it's pretty obvious that the
> > driver is traversing and caching every single row.
> > Maybe there is a configuration that disables this behaviour ?
> >
> > Daniel
> >
> > On Sun, Jun 28, 2009 at 12:04 PM, David E Jones <[hidden email]> wrote:
> >
> >> 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 <[hidden email]> 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 <
> >>>>> [hidden email]
> >>>>>
> >>>>>  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 <
> >>>>>> [hidden email]> 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
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>
> >
>
> --
> BJ Freeman
> http://www.businessesnetwork.com/automation
> http://bjfreeman.elance.com
>
> http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro
> Systems Integrator.
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Slow EntityListIterator

BJ Freeman
In reply to this post by BJ Freeman
Just a side note on slow. from a pure DB perspective I have found adding
indexes especially on joins speeds up things.
I only tested on 100K+ records.
Postgresql and MsSQL

Daniel Riquelme sent the following on 6/29/2009 5:50 PM:

> Thanks BJ I'll take a look at that link.
> Nevertheless I've been thinking about this possible problem with oracle and
> I became concerned with the fact that oracle is one of the most used
> database engines in production today.
> I'm aware that no one else is reporting this issue so I'm confident that the
> problem must be configuration related.
> I will test this with postgres to see what happens.
> The MySQL driver has the same slow behaviour.
>
> Thanks a lot for the commenst so far
>
> On Mon, Jun 29, 2009 at 1:23 PM, BJ Freeman <[hidden email]> wrote:
>
>> not sure if this is going to help. I am not an oracle person.
>> but since this does not happen on other DB, I did a search on google for
>> oracle get last
>> one solution i found used a Select max(fieldname)
>> here is the thread
>> http://www.daniweb.com/forums/thread93986.html#
>>
>> I would suggest you ask on Oracle support.
>>
>>
>> Daniel Riquelme sent the following on 6/29/2009 9:05 AM:
>>> Hi David, I have tested DataDirect driver with the same slow result.
>>> I must be missing something.
>>> I have checked that caching of every row occurs by debugging. Obviously,
>> I
>>> don't have the code to any of the jdbc drivers in question but I'm still
>>> able to see what methods are been called and it's pretty obvious that the
>>> driver is traversing and caching every single row.
>>> Maybe there is a configuration that disables this behaviour ?
>>>
>>> Daniel
>>>
>>> On Sun, Jun 28, 2009 at 12:04 PM, David E Jones <[hidden email]> wrote:
>>>
>>>> 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 <[hidden email]> 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 <
>>>>>>> [hidden email]
>>>>>>>
>>>>>>>  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 <
>>>>>>>> [hidden email]> 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
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>> --
>> BJ Freeman
>> http://www.businessesnetwork.com/automation
>> http://bjfreeman.elance.com
>>
>> http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro
>> Systems Integrator.
>>
>>
>

--
BJ Freeman
http://www.businessesnetwork.com/automation
http://bjfreeman.elance.com
http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro
Systems Integrator.

Reply | Threaded
Open this post in threaded view
|

RE: Slow EntityListIterator

Abhai Chaudhary
In reply to this post by Daniel Riquelme
Few areas we need to investigate

* Size of the table
* Indexes (e.g., there are 2 filters and one ORDER BY) => preferably,
index should be on STATUS_ID => indexes for SELECT have to be balanced
with possible impact on INSERT operations, especially if there are
periodic bulk INSERT with the index getting rebuilt for each row as
opposed to each INSERT statement
* Recency of table statistics (is the database/table analyzed regularly)
* Database parameter settings
* What is the location of the "Out of Memory" error?

-Abhai

-----Original Message-----
From: Daniel Riquelme [mailto:[hidden email]]
Sent: Saturday, June 27, 2009 5:50 PM
To: [hidden email]
Subject: Re: Slow EntityListIterator

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
<[hidden email]
> 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 <
> [hidden email]> 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
>>
>
>

http://www.mindtree.com/email/disclaimer.html



http://www.mindtree.com/email/disclaimer.html