Scott, It would be helpful if you could post your script in a Jira issues so we can run it against various databases. I would like to try it on ours.
-Adrian --- On Tue, 8/25/09, Scott Gray <[email protected]> wrote: > From: Scott Gray <[email protected]> > Subject: EntityListIterator.getResultsSizeAfterPartialList() vs. > delegator.getCountByCondition(...) > To: [email protected] > Date: Tuesday, August 25, 2009, 9:04 PM > 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 > >
