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 <[email protected]> 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 <[email protected]> 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 <[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 >>>>>>>>> >>>>>>>>> >>>>>>>>> >> -- >> 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.
