ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ashok Madhavan" <ashok.madha...@gmail.com>
Subject Re: N+1 and pagination problem
Date Mon, 30 Jul 2007 15:39:07 GMT
you can do a nested select ( select within a select within a select ) and
get the 'n' number of rows.

something like this :
  ( select FIELDNAME, USERID, rownum num
   from ( select A.FIELDNAME, B.USERID
    from tblA A, tblB B
    where A.configUnitID = ?
    and A.USERUID = B.userUID
    order by A.FIELDNAME ) a
  ) b where num between 50 and 60


On 7/30/07, Zoran Avtarovski <zoran@sparecreative.com> wrote:
> 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.

View raw message