You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user-java@ibatis.apache.org by Zoran Avtarovski <zo...@sparecreative.com> on 2007/07/30 16:13:38 UTC

N+1 and pagination problem

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.

Re: N+1 and pagination problem

Posted by Ashok Madhavan <as...@gmail.com>.

hi Zoran,
the syntax i sent was oracle specific. i for sure know sql server and db2
has equivalents. for mysql i am not sure.

ashok

On 7/31/07, Zoran Avtarovski <zo...@sparecreative.com> wrote:
>
> Thanks Ashok,
>
> Unfortunately MySQL doesn't support the rownum pseudo column, thanks to
> your
> pointer I was able to find a work around.
>
> If anybody is interested it involves using variables as such:
>
> SELECT @rownum:=@rownum+1 as rownum, P.*
>
> FROM (SELECT @rownum:=0) as R, primary_table as P
>
> It's a simple step to go from there to your solution.
>
> Thanks again, you a godsend.
>
> Z.
>
>
> > 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 from
> >   ( 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
> >
> > regards
> > ashok
> >
> > On 7/30/07, Zoran Avtarovski <zo...@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.
> >>
> >>
> >>
>
>
>

Re: N+1 and pagination problem

Posted by Zoran Avtarovski <zo...@sparecreative.com>.

Thanks Ashok,

Unfortunately MySQL doesn't support the rownum pseudo column, thanks to your
pointer I was able to find a work around.

If anybody is interested it involves using variables as such:

SELECT @rownum:=@rownum+1 as rownum, P.*

FROM (SELECT @rownum:=0) as R, primary_table as P

It's a simple step to go from there to your solution.

Thanks again, you a godsend.

Z.


> 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 from
>   ( 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
> 
> regards
> ashok
> 
> On 7/30/07, Zoran Avtarovski <zo...@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.
>> 
>> 
>>

Re: N+1 and pagination problem

Posted by Ashok Madhavan <as...@gmail.com>.

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 from
  ( 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

regards
ashok

On 7/30/07, Zoran Avtarovski <zo...@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.
>
>
>