I've come across an issue using iBatis' n+1 implementation in combination
external paging with DisplayTag. The issue lies with with the SQL LIMIT and
OFFSET commands on complex queries.
My problem is that when I execute a count statement:
SELECT COUNT(*) from primary_table
I get a resulting integer
But when I do the actual query:
SELECT * from primary_table
LEFT OUTER JOIN secondary_table
LEFT OUTER JOIN tertiary_table
LIMIT #limit# OFFSET #offset#
The limit and ofsset apply to the raw resultset, the size of which is
primary size + secondary size + tertiary size. Meaning that The DisplayTag
navigation header never lets the user get the results beyond primary size on
the raw query.
The work around I'm using is to execute the count on the complex query, but
the navigation info is misleading and I'd rather get it right.
I was hoping that somebody had come across this and had a possible solution.
The reason we've moved away from DisplayTag's built in paging is that the
result set is at 100K and growing which was giving us a performance hit.
Z.
