On Wed, Apr 11, 2012 at 9:59 PM, Dan Retzlaff <[email protected]> wrote:
> For SQL-based views, the application complexity comes from the need to > construct a count(*) query with exactly the same criteria as the subsequent > result query. In my experience, this pollutes DAO interfaces and > IDataProvider implementation non-trivially. We initially had separate > methods for counting and querying (same args), but eventually moved to a > single method that returns a <List,Integer>-tuple with both the results and > total size which our IDataProvider caches. This lets us do some Hibernate > trickery to introduce a MySQL SQL_CALC_FOUND_ROWS query hint, avoiding > separate count/results queries in most cases. It's still not simple, and > for large counts is still expensive. > > I faced something similar on an earlier project, just using straight JDBC (in Spring), and I ended up taking advice from a book; I made my query SELECT ..., COUNT(*) AS ct FROM ... WHERE ... and I added a ct variable to the class I was mapping to. A lot of extra data was returned, but it didn't slow things too much. I dout this would work well with a NoSQL database, and it is clumsy. Eric
