ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Zoran Avtarovski <zo...@sparecreative.com>
Subject N+1 and pagination problem
Date Mon, 30 Jul 2007 14:13:38 GMT
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.


View raw message