EntityListIterator.getResultsSizeAfterPartialList() vs. delegator.getCountByCondition(...)

Posted by Scott Gray-2 on Aug 26, 2009; 4:04am
URL: http://ofbiz.116.s1.nabble.com/EntityListIterator-getResultsSizeAfterPartialList-vs-delegator-getCountByCondition-tp206959.html

Hi all,

We've had a few slow query problems lately and I've narrowed it down  
to the ResultSet.last() method call in EntityListIterator when  
performed on large result sets.  I switched the FindGeneric.groovy  
script in webtools to use findCountByCondition instead of  
getResultSizeAfterPartialList and the page load time went from 40-50s  
down to 2-3s for a result containing 700,000 records.

Based on that I assumed there was probably some magic number depending  
on your system where it becomes more efficient to do a separate count  
query rather than use the ResultSet so I put together a quick test to  
find out.  I threw together a script that repeatedly adds 500 rows to  
the jobsandbox and then outputs the average time taken of 3 attempts  
to get the list size for each method.  Here's a graph of the results  
using embedded Derby: http://imgur.com/ieR7m

So unless the magic number lies somewhere in the first 500 records it  
looks to me like it always more efficient to do a separate count query.

It makes me wonder if we should be taking a different approach to  
pagination in the form widget and in general.  Any thoughts?

Thanks
Scott