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