findListIteratorByCondition performance

[cjhowe]

I've recently been playing with a data set that has about 4 million
records in the Person entity and am having some performance issues in
the Entity Data Maintenance portion of Webtools.

The only search term is partyTypeId="PERSON"

The bottleneck is the resultEli = delegator...  It takes about 30
seconds

efo.setResultSetType(EntityFindOptions.TYPE_SCROLL_INSENSITIVE);
EntityListIterator resultEli = null;
resultEli = delegator.findListIteratorByCondition(entityName,
condition, null, null, null, efo);
resultPartialList = resultEli.getPartialList(lowIndex, highIndex -
lowIndex + 1);

running the following query from the mysql command line take 0.80
seconds
SELECT * FROM person WHERE party_type_id="PERSON" limit 0,200;

Can anyone offer assistance as to why the discrepency?

[BJ Freeman]

just curious for a 4 million records
lowIndex, highIndex - lowIndex + 1
1, 4million- 1+1 = 4million.
the other is for 200 only.


Chris Howe sent the following on 12/31/2007 5:24 PM:

> I've recently been playing with a data set that has about 4 million
> records in the Person entity and am having some performance issues in
> the Entity Data Maintenance portion of Webtools.
>
> The only search term is partyTypeId="PERSON"
>
> The bottleneck is the resultEli = delegator...  It takes about 30
> seconds
>
> efo.setResultSetType(EntityFindOptions.TYPE_SCROLL_INSENSITIVE);
> EntityListIterator resultEli = null;
> resultEli = delegator.findListIteratorByCondition(entityName,
> condition, null, null, null, efo);
> resultPartialList = resultEli.getPartialList(lowIndex, highIndex -
> lowIndex + 1);
>
> running the following query from the mysql command line take 0.80
> seconds
> SELECT * FROM person WHERE party_type_id="PERSON" limit 0,200;
>
> Can anyone offer assistance as to why the discrepency?
>
>
>
>

[cjhowe]

The webtools entity maint paginates 50 records @ a time


--- BJ Freeman <[hidden email]> wrote:

> just curious for a 4 million records
> lowIndex, highIndex - lowIndex + 1
> 1, 4million- 1+1 = 4million.
> the other is for 200 only.
>
>
> Chris Howe sent the following on 12/31/2007 5:24 PM:
> > I've recently been playing with a data set that has about 4 million
> > records in the Person entity and am having some performance issues
> in
> > the Entity Data Maintenance portion of Webtools.
> >
> > The only search term is partyTypeId="PERSON"
> >
> > The bottleneck is the resultEli = delegator...  It takes about 30
> > seconds
> >
> > efo.setResultSetType(EntityFindOptions.TYPE_SCROLL_INSENSITIVE);
> > EntityListIterator resultEli = null;
> > resultEli = delegator.findListIteratorByCondition(entityName,
> > condition, null, null, null, efo);
> > resultPartialList = resultEli.getPartialList(lowIndex, highIndex -
> > lowIndex + 1);
> >
> > running the following query from the mysql command line take 0.80
> > seconds
> > SELECT * FROM person WHERE party_type_id="PERSON" limit 0,200;
> >
> > Can anyone offer assistance as to why the discrepency?
> >
> >
> >
> >
>
>

[BJ Freeman]

I  change the qty in the URL to get higher number per page.
assumed you did the same.
:)

Chris Howe sent the following on 12/31/2007 8:14 PM:

> The webtools entity maint paginates 50 records @ a time
>
>
> --- BJ Freeman <[hidden email]> wrote:
>
>> just curious for a 4 million records
>> lowIndex, highIndex - lowIndex + 1
>> 1, 4million- 1+1 = 4million.
>> the other is for 200 only.
>>
>>
>> Chris Howe sent the following on 12/31/2007 5:24 PM:
>>> I've recently been playing with a data set that has about 4 million
>>> records in the Person entity and am having some performance issues
>> in
>>> the Entity Data Maintenance portion of Webtools.
>>>
>>> The only search term is partyTypeId="PERSON"
>>>
>>> The bottleneck is the resultEli = delegator...  It takes about 30
>>> seconds
>>>
>>> efo.setResultSetType(EntityFindOptions.TYPE_SCROLL_INSENSITIVE);
>>> EntityListIterator resultEli = null;
>>> resultEli = delegator.findListIteratorByCondition(entityName,
>>> condition, null, null, null, efo);
>>> resultPartialList = resultEli.getPartialList(lowIndex, highIndex -
>>> lowIndex + 1);
>>>
>>> running the following query from the mysql command line take 0.80
>>> seconds
>>> SELECT * FROM person WHERE party_type_id="PERSON" limit 0,200;
>>>
>>> Can anyone offer assistance as to why the discrepency?
>>>
>>>
>>>
>>>
>>
>
>
>
>

[David E Jones]

Have you added timing to see which part of this is taking a long time?  
The main candidates would probably be the findListIteratorByCondition  
call and the getPartialList call.

You could also change it to not do the scroll insensitive... MySQL  
might be doing something funny with that...

-David


On Dec 31, 2007, at 6:24 PM, Chris Howe wrote:

> I've recently been playing with a data set that has about 4 million
> records in the Person entity and am having some performance issues in
> the Entity Data Maintenance portion of Webtools.
>
> The only search term is partyTypeId="PERSON"
>
> The bottleneck is the resultEli = delegator...  It takes about 30
> seconds
>
> efo.setResultSetType(EntityFindOptions.TYPE_SCROLL_INSENSITIVE);
> EntityListIterator resultEli = null;
> resultEli = delegator.findListIteratorByCondition(entityName,
> condition, null, null, null, efo);
> resultPartialList = resultEli.getPartialList(lowIndex, highIndex -
> lowIndex + 1);
>
> running the following query from the mysql command line take 0.80
> seconds
> SELECT * FROM person WHERE party_type_id="PERSON" limit 0,200;
>
> Can anyone offer assistance as to why the discrepency?
>

[BJ Freeman]

Chris I would like to work on this with you.
I have a different DB, but large amount of records.
I suggest that we do an java.recordset test like your direct call to mydb.
1)connect to db
2)make SQL execute to return the first recordset using cursors.
3)then another call that moves the cursor by x number of records.

this will give us a reference from java through the driver to the db.
then compare that to the calls for the same number of records through
the code your using.
The will give us a delta of what ofbiz is adding to to the timing and a
benchmark to work on code.

It may turn out that there is something funky about the way mydb works.
I read something like it max record count 5.0 is about 4 million.
Since to to cursor and know the end of the recordset, it must find them
all.  if the number of records you have is close the max record set that
may cause a problem.

Chris Howe sent the following on 12/31/2007 5:24 PM:

> I've recently been playing with a data set that has about 4 million
> records in the Person entity and am having some performance issues in
> the Entity Data Maintenance portion of Webtools.
>
> The only search term is partyTypeId="PERSON"
>
> The bottleneck is the resultEli = delegator...  It takes about 30
> seconds
>
> efo.setResultSetType(EntityFindOptions.TYPE_SCROLL_INSENSITIVE);
> EntityListIterator resultEli = null;
> resultEli = delegator.findListIteratorByCondition(entityName,
> condition, null, null, null, efo);
> resultPartialList = resultEli.getPartialList(lowIndex, highIndex -
> lowIndex + 1);
>
> running the following query from the mysql command line take 0.80
> seconds
> SELECT * FROM person WHERE party_type_id="PERSON" limit 0,200;
>
> Can anyone offer assistance as to why the discrepency?
>
>
>
>

[BJ Freeman]

also, at least for the db's I use there is a this type of code in ofbiz
SET @skip=1; SET @numrows=5;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?';
EXECUTE STMT USING @skip, @numrows;
this above was taken from
http://dev.mysql.com/doc/refman/5.0/en/select.html

BJ Freeman sent the following on 1/1/2008 3:25 AM:

> Chris I would like to work on this with you.
> I have a different DB, but large amount of records.
> I suggest that we do an java.recordset test like your direct call to mydb.
> 1)connect to db
> 2)make SQL execute to return the first recordset using cursors.
> 3)then another call that moves the cursor by x number of records.
>
> this will give us a reference from java through the driver to the db.
> then compare that to the calls for the same number of records through
> the code your using.
> The will give us a delta of what ofbiz is adding to to the timing and a
> benchmark to work on code.
>
> It may turn out that there is something funky about the way mydb works.
> I read something like it max record count 5.0 is about 4 million.
> Since to to cursor and know the end of the recordset, it must find them
> all.  if the number of records you have is close the max record set that
> may cause a problem.
>
> Chris Howe sent the following on 12/31/2007 5:24 PM:
>> I've recently been playing with a data set that has about 4 million
>> records in the Person entity and am having some performance issues in
>> the Entity Data Maintenance portion of Webtools.
>>
>> The only search term is partyTypeId="PERSON"
>>
>> The bottleneck is the resultEli = delegator...  It takes about 30
>> seconds
>>
>> efo.setResultSetType(EntityFindOptions.TYPE_SCROLL_INSENSITIVE);
>> EntityListIterator resultEli = null;
>> resultEli = delegator.findListIteratorByCondition(entityName,
>> condition, null, null, null, efo);
>> resultPartialList = resultEli.getPartialList(lowIndex, highIndex -
>> lowIndex + 1);
>>
>> running the following query from the mysql command line take 0.80
>> seconds
>> SELECT * FROM person WHERE party_type_id="PERSON" limit 0,200;
>>
>> Can anyone offer assistance as to why the discrepency?
>>
>>
>>
>>
>
>
>
>