Ok, just a quick question about MySQL here, in case anyone knows it well offhand. Does how does
MySQL cursors perform compared to using plain SQL LIMIT? Bump me off to a new thread if this is
gonna draw too many responses. (Is it off-topic?).
> This is incorrect. The EntityListIterator uses a database cursor and
> keeps the connection open to the database. Depending on what the
> database and JDBC driver support and how things are configured, it will
> typically pull over 100 records at a time over the network as it scrolls
> through or jumps around the result set.
Sounds very database-dependent. Will it make things worse if we used SQL LIMIT?
> As to your specific performance problems, with that information I have
> no idea what the problem might be. It depends on how you have deployed
> OFBiz, how the database and JDBC driver are setup, and if the custom or
> OOTB OFBiz code is written properly to use the EntityListIterator.
I found the performance problem using low-level MySQL functions. I suspect MySQL (4.x) doesn't do
resultset scrolls as efficiently as when using SQL LIMIT, not sure.
Jonathon
David E. Jones wrote:
This is incorrect. The EntityListIterator uses a database cursor and
keeps the connection open to the database. Depending on what the
database and JDBC driver support and how things are configured, it will
typically pull over 100 records at a time over the network as it scrolls
through or jumps around the result set.
As to your specific performance problems, with that information I have
no idea what the problem might be. It depends on how you have deployed
OFBiz, how the database and JDBC driver are setup, and if the custom or
OOTB OFBiz code is written properly to use the EntityListIterator.
-David
On Jan 31, 2007, at 4:53 PM, Jonathon -- Improov wrote:
I could be wrong, but it seems OFBiz pulls down all records (1000s
possibly) before putting all those records through that function.
Yeah, I know, the partial list is gleaned off of the resultset and so
it seems that we're not exactly swallowing all records first.
But isn't it more database-independent (or less?) to use SQL LIMIT?
I'm seeing long load times for LookUpProduct service when listing for
pagination a mere 1000 records. Perhaps it'll be much much faster
using SQL LIMIT? I know for a fact that using SQL LIMIT is faster than
scrolling through a resultset, know that based on my own apps
(currently having entity framework that provides for SQL LIMIT).
Jonathon
David E. Jones wrote:
Could you explain how that would be different and better than using
the EntityListIterator.getPartialList method?
-David
On Jan 31, 2007, at 11:21 AM, Leon Torres wrote:
Hi David,
Sorry, maybe I should have expressed this in the form of two questions.
Is there a way to do a query with OFFSET and LIMIT using
EntityCondition?
If not, then is there a way to do these offset and limit operations
with findEntityListIteratorByCondition?
- Leon
David E. Jones wrote:
What's wrong with the stuff that's been there for years on the
EntityListIterator?
-David
On Jan 30, 2007, at 4:52 PM, Leon Torres wrote:
Hi folks,
I think we really need to be able to specify the size of the list
we want and the index to start at for the
GenericDelegator.findByAnd and findByCondition methods.
The idea is to support pagination in the form widgets and similar
systems for lists of data that cannot be supported by
<view-entity>. For example, if the inventory QOH and ATP are
required for a form-widget list, we need to call the
getInventoryAvailableByFacility service and add the results to
each list row. Another example would be a union of various
entities together, some of which need heuristics to select the data.
It should be relatively simple: Create a method that wraps a call
to findListIteratorByCondition, then grab the desired range of
results. It should also return the size of the table.
Then, as an example, we can call these methods with our viewSize
and viewIndex parameters, build our complex list of data based on
the results, and use the form-widget's override-list-size to make
pagination work with it.
Thoughts?
- Leon
