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
smime.p7s
Description: S/MIME cryptographic signature
