Re: Processing 1000 items in Orders takes too long

[Scott Gray]

Usually the reason for the slow query time when using the iterator is because your database isn't actually capable of providing cursors and it is actually the entire result set being loaded. For example MySQL will only use a cursor if you use a forward only result + you need to set your fetch size to Integer.MIN_VALUE (or whatever that constant is called). In those cases where you don't have a cursor using the entity iterator isn't much different from not using it.

Regards
Scott

On 11/11/2009, at 9:41 PM, ian tabangay wrote:

Setting max rows is the main way to get around these sorts of problems. Aside from the non standardness of using an offset, the only time I've really seen a good potential use for it is in list pagination but even then if a user is attempting to view page 1000 then I think it's more of a UI issue than anything else. IMO if the user can quickly view any of the first maybe 50 odd pages then that it perfectly fine.

Hi Scott,


we had problems dealing with the current implemtation of entity list

number of result sets as the total number of rows grow to the hundred

for us since we wouldnt be changing our databases anyways.


---
Ian Tabangay

Actually, my main concern is more on the processing of a document. This usually involves alot of inserts and updates at the very least. I think the bottleneck is more in the database inserts/updates. Indeed, scaling up our hardware (and alot of database load balancing/clustering and indexes) could help speed up these processes. As for querying a large set of result sets, iterator. There was a linear time difference for requesting for a small thousands. We modified the sql processor to use limit and offset to make requesting time constant regardless of the number of rows the entity would have. The limitation of using a "non standard" sql command was acceptable On Wed, Nov 11, 2009 at 4:02 PM, Scott Gray <scott.g...@hotwaxmedia.com >wrote:

Hi Ian,


lists for large result sets, improving queries to make better use of
indexes, etc.  After that it really just comes down to your hardware.

Regards
Scott

I didn't say OFBiz wasn't intended to handle this sort of load, I just said that the purchase order code may not have been optimized for it. I have no doubt that OFBiz can handle what you're describing but you may find portions of the code could be improved to speed things up a bit, like using the cache where appropriate, using the entity iterator rather than retrieving full

smime.p7s
Description: S/MIME cryptographic signature