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 Tue, 31 Jul 2007 02:31:59 GMT
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 <zoran@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 <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.
> >>
> >>
> >>
>
>
>

Mime
View raw message