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 Alin Popa <al...@gmail.com> on 2009/05/15 18:10:15 UTC
iBatis - MySQL pagination
Hi,
I'm using iBatis with MySQL and also trying to do some pagination:
return getSqlMapClientTemplate().queryForList("getAll", skipResults,
maxRecords);
and the "getAll" query:
<select id="getAllVJobs" resultMap="simpleResult">
SELECT * FROM mytable
</select>
Indeed, the pagination seems to work fine, BUT in mysql logs I see
executed the entire "SELECT * FROM mytable" which is a full table
scan.
Doesn't seems very right to me.
Is there a catch behind this idea ?
Is fetching all records and after that iterate through them ? (I don't
think it's very nice this way).
Any ideas ?
Thanks.
Alin
Re: iBatis - MySQL pagination
Posted by Alin Popa <al...@gmail.com>.
Can anyone please suggest me a link to ibatis documentation regarding
$substitution$ ?
Thanks.
On Fri, May 15, 2009 at 7:15 PM, Larry Meadors <la...@gmail.com> wrote:
> Do it in the sql statement instead of using pagination in ibatis.
>
> Look at limit, row_count and offset here:
>
> http://dev.mysql.com/doc/refman/5.0/en/select.html
>
> You will probably need to use $substitution$ for the parameters, but
> surely some mysql stud can tell you more about that than I can. :)
>
> Larry
>
--
Best Regards,
Alin
Re: iBatis - MySQL pagination
Posted by Clinton Begin <cl...@gmail.com>.
Here's the skip results logic from iBATIS 2.0:
// Skip Results
if (rs.getType() != ResultSet.TYPE_FORWARD_ONLY) {
if (skipResults > 0) {
rs.absolute(skipResults);
}
} else {
for (int i = 0; i < skipResults; i++) {
if (!rs.next()) {
return;
}
}
}
If your database supports cursors via JDBC, this is a very efficient
operation. It's also efficient for smart drivers that don't retrieve data
on rs.next(), but instead when you actually call rs.getXxxxx(). Otherwise,
you should seek to use database specific limiters like MySQL OFFSET/LIMIT an
oracle rownum.
Clinton
On Fri, May 15, 2009 at 12:38 PM, Clinton Begin <cl...@gmail.com>wrote:
> For everyone saying that "iBATIS shouldn't do this": The reason it does is
> that JDBC provides an API for it. iBATIS generally defines its scope by
> what JDBC allows for (think of iBATIS as an easier to use JDBC).
> So generally, if it's available in JDBC, we support it (cautiously). If
> there's a better, database specific feature that's accessible via SQL, then
> you're also able to leverage that and ignore the iBATIS pagination (for
> example).
>
> If it's specific to the vendor's driver API, then we don't support it.
>
> Cheers,
> Clinton
>
>
> On Fri, May 15, 2009 at 11:12 AM, Brandon Goodin <brandon.goodin@gmail.com
> > wrote:
>
>> I really think you should frame your problem more clearly before you come
>> up with a solution :)
>>
>> Paging is an interesting thing and there really isn't a "one size fits
>> all" solution. It depends on your requirements. If you want help figuring
>> out the most performant way to handle paging in your project then you should
>> define how the paging will be used. At that point you can talk about the
>> best optimization strategy. Without that I think we are just throwing ideas
>> at a wall to see if they stick.
>>
>> On a sidenote using select * from some_table is a generally a bad idea.
>> You really should specify the columns that you want returned.
>>
>> Brandon
>>
>>
>>
>> On Fri, May 15, 2009 at 11:59 AM, Alin Popa <al...@gmail.com> wrote:
>>
>>> I wanted to say that is retrieving all the data from database (into
>>> memory) and after that is iterating over it. What is happening when
>>> "SELECT * FROM mytable" is returning 5 MIL records ? For sure I don't
>>> want that. I don't know if mysql knows how to do optimization without
>>> "helping" it somehow (LIMIT or something else).
>>>
>>> On Fri, May 15, 2009 at 7:52 PM, Brandon Goodin
>>> <br...@gmail.com> wrote:
>>> > Where are you seeing it perform a full table scan? select * does not
>>> > *necessarily* mean it will load all of your records into memory. Oracle
>>> for
>>> > example is smart about this and I would assume that most databases have
>>> > followed suit in this sense over the last few years. Most databases
>>> will
>>> > smartly use their indexes to determine what gets loaded and how much.
>>> It's
>>> > really the reason why you don't know how large your resultset is until
>>> it is
>>> > actually fully loaded. You'll be guaranteed a full table scan if you
>>> were to
>>> > sort on a column that is derived from a function call.
>>> >
>>> > Another route on this may be to actually look at you indexing strategy.
>>> >
>>> > Brandon
>>> >
>>> >
>>> > On Fri, May 15, 2009 at 11:27 AM, Bhaarat Sharma <bh...@gmail.com>
>>> > wrote:
>>> >>
>>> >> what if we have a stored procedures that takes in 'startrow' and
>>> 'endrow'
>>> >> as parameters. Based on those it returns the results back.
>>> >> If a sp like that were to be called using iBatis with pagination then
>>> will
>>> >> the SP be called again to get NEXT set of results?
>>> >>
>>> >> On Fri, May 15, 2009 at 12:15 PM, Larry Meadors <
>>> larry.meadors@gmail.com>
>>> >> wrote:
>>> >>>
>>> >>> Do it in the sql statement instead of using pagination in ibatis.
>>> >>>
>>> >>> Look at limit, row_count and offset here:
>>> >>>
>>> >>> http://dev.mysql.com/doc/refman/5.0/en/select.html
>>> >>>
>>> >>> You will probably need to use $substitution$ for the parameters, but
>>> >>> surely some mysql stud can tell you more about that than I can. :)
>>> >>>
>>> >>> Larry
>>> >>
>>> >
>>> >
>>>
>>>
>>>
>>> --
>>> Best Regards,
>>>
>>> Alin
>>>
>>
>>
>
Re: iBatis - MySQL pagination
Posted by Clinton Begin <cl...@gmail.com>.
For everyone saying that "iBATIS shouldn't do this": The reason it does is
that JDBC provides an API for it. iBATIS generally defines its scope by
what JDBC allows for (think of iBATIS as an easier to use JDBC).
So generally, if it's available in JDBC, we support it (cautiously). If
there's a better, database specific feature that's accessible via SQL, then
you're also able to leverage that and ignore the iBATIS pagination (for
example).
If it's specific to the vendor's driver API, then we don't support it.
Cheers,
Clinton
On Fri, May 15, 2009 at 11:12 AM, Brandon Goodin
<br...@gmail.com>wrote:
> I really think you should frame your problem more clearly before you come
> up with a solution :)
>
> Paging is an interesting thing and there really isn't a "one size fits all"
> solution. It depends on your requirements. If you want help figuring out the
> most performant way to handle paging in your project then you should define
> how the paging will be used. At that point you can talk about the best
> optimization strategy. Without that I think we are just throwing ideas at a
> wall to see if they stick.
>
> On a sidenote using select * from some_table is a generally a bad idea. You
> really should specify the columns that you want returned.
>
> Brandon
>
>
>
> On Fri, May 15, 2009 at 11:59 AM, Alin Popa <al...@gmail.com> wrote:
>
>> I wanted to say that is retrieving all the data from database (into
>> memory) and after that is iterating over it. What is happening when
>> "SELECT * FROM mytable" is returning 5 MIL records ? For sure I don't
>> want that. I don't know if mysql knows how to do optimization without
>> "helping" it somehow (LIMIT or something else).
>>
>> On Fri, May 15, 2009 at 7:52 PM, Brandon Goodin
>> <br...@gmail.com> wrote:
>> > Where are you seeing it perform a full table scan? select * does not
>> > *necessarily* mean it will load all of your records into memory. Oracle
>> for
>> > example is smart about this and I would assume that most databases have
>> > followed suit in this sense over the last few years. Most databases will
>> > smartly use their indexes to determine what gets loaded and how much.
>> It's
>> > really the reason why you don't know how large your resultset is until
>> it is
>> > actually fully loaded. You'll be guaranteed a full table scan if you
>> were to
>> > sort on a column that is derived from a function call.
>> >
>> > Another route on this may be to actually look at you indexing strategy.
>> >
>> > Brandon
>> >
>> >
>> > On Fri, May 15, 2009 at 11:27 AM, Bhaarat Sharma <bh...@gmail.com>
>> > wrote:
>> >>
>> >> what if we have a stored procedures that takes in 'startrow' and
>> 'endrow'
>> >> as parameters. Based on those it returns the results back.
>> >> If a sp like that were to be called using iBatis with pagination then
>> will
>> >> the SP be called again to get NEXT set of results?
>> >>
>> >> On Fri, May 15, 2009 at 12:15 PM, Larry Meadors <
>> larry.meadors@gmail.com>
>> >> wrote:
>> >>>
>> >>> Do it in the sql statement instead of using pagination in ibatis.
>> >>>
>> >>> Look at limit, row_count and offset here:
>> >>>
>> >>> http://dev.mysql.com/doc/refman/5.0/en/select.html
>> >>>
>> >>> You will probably need to use $substitution$ for the parameters, but
>> >>> surely some mysql stud can tell you more about that than I can. :)
>> >>>
>> >>> Larry
>> >>
>> >
>> >
>>
>>
>>
>> --
>> Best Regards,
>>
>> Alin
>>
>
>
Re: iBatis - MySQL pagination
Posted by Brandon Goodin <br...@gmail.com>.
I really think you should frame your problem more clearly before you come up
with a solution :)
Paging is an interesting thing and there really isn't a "one size fits all"
solution. It depends on your requirements. If you want help figuring out the
most performant way to handle paging in your project then you should define
how the paging will be used. At that point you can talk about the best
optimization strategy. Without that I think we are just throwing ideas at a
wall to see if they stick.
On a sidenote using select * from some_table is a generally a bad idea. You
really should specify the columns that you want returned.
Brandon
On Fri, May 15, 2009 at 11:59 AM, Alin Popa <al...@gmail.com> wrote:
> I wanted to say that is retrieving all the data from database (into
> memory) and after that is iterating over it. What is happening when
> "SELECT * FROM mytable" is returning 5 MIL records ? For sure I don't
> want that. I don't know if mysql knows how to do optimization without
> "helping" it somehow (LIMIT or something else).
>
> On Fri, May 15, 2009 at 7:52 PM, Brandon Goodin
> <br...@gmail.com> wrote:
> > Where are you seeing it perform a full table scan? select * does not
> > *necessarily* mean it will load all of your records into memory. Oracle
> for
> > example is smart about this and I would assume that most databases have
> > followed suit in this sense over the last few years. Most databases will
> > smartly use their indexes to determine what gets loaded and how much.
> It's
> > really the reason why you don't know how large your resultset is until it
> is
> > actually fully loaded. You'll be guaranteed a full table scan if you were
> to
> > sort on a column that is derived from a function call.
> >
> > Another route on this may be to actually look at you indexing strategy.
> >
> > Brandon
> >
> >
> > On Fri, May 15, 2009 at 11:27 AM, Bhaarat Sharma <bh...@gmail.com>
> > wrote:
> >>
> >> what if we have a stored procedures that takes in 'startrow' and
> 'endrow'
> >> as parameters. Based on those it returns the results back.
> >> If a sp like that were to be called using iBatis with pagination then
> will
> >> the SP be called again to get NEXT set of results?
> >>
> >> On Fri, May 15, 2009 at 12:15 PM, Larry Meadors <
> larry.meadors@gmail.com>
> >> wrote:
> >>>
> >>> Do it in the sql statement instead of using pagination in ibatis.
> >>>
> >>> Look at limit, row_count and offset here:
> >>>
> >>> http://dev.mysql.com/doc/refman/5.0/en/select.html
> >>>
> >>> You will probably need to use $substitution$ for the parameters, but
> >>> surely some mysql stud can tell you more about that than I can. :)
> >>>
> >>> Larry
> >>
> >
> >
>
>
>
> --
> Best Regards,
>
> Alin
>
Re: iBatis - MySQL pagination
Posted by Alin Popa <al...@gmail.com>.
I wanted to say that is retrieving all the data from database (into
memory) and after that is iterating over it. What is happening when
"SELECT * FROM mytable" is returning 5 MIL records ? For sure I don't
want that. I don't know if mysql knows how to do optimization without
"helping" it somehow (LIMIT or something else).
On Fri, May 15, 2009 at 7:52 PM, Brandon Goodin
<br...@gmail.com> wrote:
> Where are you seeing it perform a full table scan? select * does not
> *necessarily* mean it will load all of your records into memory. Oracle for
> example is smart about this and I would assume that most databases have
> followed suit in this sense over the last few years. Most databases will
> smartly use their indexes to determine what gets loaded and how much. It's
> really the reason why you don't know how large your resultset is until it is
> actually fully loaded. You'll be guaranteed a full table scan if you were to
> sort on a column that is derived from a function call.
>
> Another route on this may be to actually look at you indexing strategy.
>
> Brandon
>
>
> On Fri, May 15, 2009 at 11:27 AM, Bhaarat Sharma <bh...@gmail.com>
> wrote:
>>
>> what if we have a stored procedures that takes in 'startrow' and 'endrow'
>> as parameters. Based on those it returns the results back.
>> If a sp like that were to be called using iBatis with pagination then will
>> the SP be called again to get NEXT set of results?
>>
>> On Fri, May 15, 2009 at 12:15 PM, Larry Meadors <la...@gmail.com>
>> wrote:
>>>
>>> Do it in the sql statement instead of using pagination in ibatis.
>>>
>>> Look at limit, row_count and offset here:
>>>
>>> http://dev.mysql.com/doc/refman/5.0/en/select.html
>>>
>>> You will probably need to use $substitution$ for the parameters, but
>>> surely some mysql stud can tell you more about that than I can. :)
>>>
>>> Larry
>>
>
>
--
Best Regards,
Alin
Re: iBatis - MySQL pagination
Posted by Brandon Goodin <br...@gmail.com>.
Where are you seeing it perform a full table scan? select * does not
*necessarily* mean it will load all of your records into memory. Oracle for
example is smart about this and I would assume that most databases have
followed suit in this sense over the last few years. Most databases will
smartly use their indexes to determine what gets loaded and how much. It's
really the reason why you don't know how large your resultset is until it is
actually fully loaded. You'll be guaranteed a full table scan if you were to
sort on a column that is derived from a function call.
Another route on this may be to actually look at you indexing strategy.
Brandon
On Fri, May 15, 2009 at 11:27 AM, Bhaarat Sharma <bh...@gmail.com>wrote:
> what if we have a stored procedures that takes in 'startrow' and 'endrow'
> as parameters. Based on those it returns the results back.
> If a sp like that were to be called using iBatis with pagination then will
> the SP be called again to get NEXT set of results?
>
>
> On Fri, May 15, 2009 at 12:15 PM, Larry Meadors <la...@gmail.com>wrote:
>
>> Do it in the sql statement instead of using pagination in ibatis.
>>
>> Look at limit, row_count and offset here:
>>
>> http://dev.mysql.com/doc/refman/5.0/en/select.html
>>
>> You will probably need to use $substitution$ for the parameters, but
>> surely some mysql stud can tell you more about that than I can. :)
>>
>> Larry
>>
>
>
Re: iBatis - MySQL pagination
Posted by Bhaarat Sharma <bh...@gmail.com>.
what if we have a stored procedures that takes in 'startrow' and 'endrow' as
parameters. Based on those it returns the results back.
If a sp like that were to be called using iBatis with pagination then will
the SP be called again to get NEXT set of results?
On Fri, May 15, 2009 at 12:15 PM, Larry Meadors <la...@gmail.com>wrote:
> Do it in the sql statement instead of using pagination in ibatis.
>
> Look at limit, row_count and offset here:
>
> http://dev.mysql.com/doc/refman/5.0/en/select.html
>
> You will probably need to use $substitution$ for the parameters, but
> surely some mysql stud can tell you more about that than I can. :)
>
> Larry
>
Re: iBatis - MySQL pagination
Posted by Larry Meadors <la...@gmail.com>.
IMO, ibatis shouldn't do this, but it's part of the API now, so it does. :-)
Larry
On Fri, May 15, 2009 at 10:39 AM, Alin Popa <al...@gmail.com> wrote:
> My dilemma is: why ibatis is doing this kind of abstraction if on the
> underlying server is doing it wrong ? Might cause confusion and of
> course, bad performances. I asked the question here, because I thought
> that I'm missing something related to this pagination issue.
Re: iBatis - MySQL pagination
Posted by Alex Sherwin <al...@acadiasoft.com>.
I'm not an expert, but I think the answer is that standard SQL doesn't
provide this mechanism, making it's implementation database dependent.
iBATIS is providing a mechanism for skip/max that will work with any DB
(quite possibly inefficiently), but at least the feature is there.
To be more efficient, you just need to do it per DB like you've noted
with MySQL and LIMIT
Alin Popa wrote:
> Thanks Larry for your quick response.
>
> If I'm doing it in Mysql statement, I'll do it using LIMIT which is
> taking exactly the same 2 params like ibatis (skipResults and
> maxRecords).
>
> My dilemma is: why ibatis is doing this kind of abstraction if on the
> underlying server is doing it wrong ? Might cause confusion and of
> course, bad performances. I asked the question here, because I thought
> that I'm missing something related to this pagination issue.
>
>
> On Fri, May 15, 2009 at 7:15 PM, Larry Meadors <la...@gmail.com> wrote:
>
>> Do it in the sql statement instead of using pagination in ibatis.
>>
>> Look at limit, row_count and offset here:
>>
>> http://dev.mysql.com/doc/refman/5.0/en/select.html
>>
>> You will probably need to use $substitution$ for the parameters, but
>> surely some mysql stud can tell you more about that than I can. :)
>>
>> Larry
>>
>>
>
>
>
>
Re: iBatis - MySQL pagination
Posted by Alin Popa <al...@gmail.com>.
Thanks Larry for your quick response.
If I'm doing it in Mysql statement, I'll do it using LIMIT which is
taking exactly the same 2 params like ibatis (skipResults and
maxRecords).
My dilemma is: why ibatis is doing this kind of abstraction if on the
underlying server is doing it wrong ? Might cause confusion and of
course, bad performances. I asked the question here, because I thought
that I'm missing something related to this pagination issue.
On Fri, May 15, 2009 at 7:15 PM, Larry Meadors <la...@gmail.com> wrote:
> Do it in the sql statement instead of using pagination in ibatis.
>
> Look at limit, row_count and offset here:
>
> http://dev.mysql.com/doc/refman/5.0/en/select.html
>
> You will probably need to use $substitution$ for the parameters, but
> surely some mysql stud can tell you more about that than I can. :)
>
> Larry
>
--
Best Regards,
Alin
Re: iBatis - MySQL pagination
Posted by Larry Meadors <la...@gmail.com>.
Do it in the sql statement instead of using pagination in ibatis.
Look at limit, row_count and offset here:
http://dev.mysql.com/doc/refman/5.0/en/select.html
You will probably need to use $substitution$ for the parameters, but
surely some mysql stud can tell you more about that than I can. :)
Larry
Re: iBatis - MySQL pagination
Posted by Alin Popa <al...@gmail.com>.
Well, in mysql I'm doing this way:
SELECT * FROM mytable LIMIT 0,10; -- will return first 10 records from mytable
SELECT * FROM mytable LIMIT 10,10; -- will return next 10 records from
mytable (2nd page)
and so on ...
For each page, depending on how many records for page you want, you
need to execute the select with specified LIMIT params.
Hope it helps.
On Fri, May 15, 2009 at 7:50 PM, Bhaarat Sharma <bh...@gmail.com> wrote:
> Thanks,
> But my question is lets say I want to display 10 results on a page but in
> total I want 50 results. so 10 on each page.
> I have some query like this in iBatis:
>
> SELECT rownum, table_name
> FROM user_tables
>
> WHERE rownum <=<some number I will pass in, in this case 10?>;
> so when I call this from my code. will the above query be ran 5 times??
>
> On Fri, May 15, 2009 at 12:42 PM, Alin Popa <al...@gmail.com> wrote:
>>
>> Bhaarat,
>>
>> If you're using mysql, you could do pagination using LIMIT statement,
>> which is pretty straight forward.
>> On Mssql - TOP (using cursors).
>> On Oracle - ROWNUM.
>>
>>
>> On Fri, May 15, 2009 at 7:14 PM, Bhaarat Sharma <bh...@gmail.com>
>> wrote:
>> > that is a very interesting question. We were looking into doing
>> > pagination
>> > with iBatis as well but would not want a full scan on the DB but rather
>> > only
>> > get amount of rows specified by skipResults
>> >
>> > On Fri, May 15, 2009 at 12:10 PM, Alin Popa <al...@gmail.com> wrote:
>> >>
>> >> Hi,
>> >>
>> >> I'm using iBatis with MySQL and also trying to do some pagination:
>> >>
>> >>
>> >> return getSqlMapClientTemplate().queryForList("getAll", skipResults,
>> >> maxRecords);
>> >>
>> >> and the "getAll" query:
>> >>
>> >> <select id="getAllVJobs" resultMap="simpleResult">
>> >> SELECT * FROM mytable
>> >> </select>
>> >>
>> >> Indeed, the pagination seems to work fine, BUT in mysql logs I see
>> >> executed the entire "SELECT * FROM mytable" which is a full table
>> >> scan.
>> >> Doesn't seems very right to me.
>> >>
>> >> Is there a catch behind this idea ?
>> >> Is fetching all records and after that iterate through them ? (I don't
>> >> think it's very nice this way).
>> >>
>> >> Any ideas ?
>> >>
>> >> Thanks.
>> >> Alin
>> >
>> >
>>
>>
>>
>> --
>> Best Regards,
>>
>> Alin
>
>
--
Best Regards,
Alin
Re: iBatis - MySQL pagination
Posted by Bhaarat Sharma <bh...@gmail.com>.
Thanks,
But my question is lets say I want to display 10 results on a page but in
total I want 50 results. so 10 on each page.
I have some query like this in iBatis:
SELECT rownum, table_name
FROM user_tables
WHERE rownum <=<some number I will pass in, in this case 10?>;
so when I call this from my code. will the above query be ran 5 times??
On Fri, May 15, 2009 at 12:42 PM, Alin Popa <al...@gmail.com> wrote:
> Bhaarat,
>
> If you're using mysql, you could do pagination using LIMIT statement,
> which is pretty straight forward.
> On Mssql - TOP (using cursors).
> On Oracle - ROWNUM.
>
>
> On Fri, May 15, 2009 at 7:14 PM, Bhaarat Sharma <bh...@gmail.com>
> wrote:
> > that is a very interesting question. We were looking into doing
> pagination
> > with iBatis as well but would not want a full scan on the DB but rather
> only
> > get amount of rows specified by skipResults
> >
> > On Fri, May 15, 2009 at 12:10 PM, Alin Popa <al...@gmail.com> wrote:
> >>
> >> Hi,
> >>
> >> I'm using iBatis with MySQL and also trying to do some pagination:
> >>
> >>
> >> return getSqlMapClientTemplate().queryForList("getAll", skipResults,
> >> maxRecords);
> >>
> >> and the "getAll" query:
> >>
> >> <select id="getAllVJobs" resultMap="simpleResult">
> >> SELECT * FROM mytable
> >> </select>
> >>
> >> Indeed, the pagination seems to work fine, BUT in mysql logs I see
> >> executed the entire "SELECT * FROM mytable" which is a full table
> >> scan.
> >> Doesn't seems very right to me.
> >>
> >> Is there a catch behind this idea ?
> >> Is fetching all records and after that iterate through them ? (I don't
> >> think it's very nice this way).
> >>
> >> Any ideas ?
> >>
> >> Thanks.
> >> Alin
> >
> >
>
>
>
> --
> Best Regards,
>
> Alin
>
Re: iBatis - MySQL pagination
Posted by Larry Meadors <la...@gmail.com>.
Just remember with rownum in oracle to do a subselect:
select * from (
your real select here
) where rownum ...
Larry
On Fri, May 15, 2009 at 10:42 AM, Alin Popa <al...@gmail.com> wrote:
> Bhaarat,
>
> If you're using mysql, you could do pagination using LIMIT statement,
> which is pretty straight forward.
> On Mssql - TOP (using cursors).
> On Oracle - ROWNUM.
>
>
> On Fri, May 15, 2009 at 7:14 PM, Bhaarat Sharma <bh...@gmail.com> wrote:
>> that is a very interesting question. We were looking into doing pagination
>> with iBatis as well but would not want a full scan on the DB but rather only
>> get amount of rows specified by skipResults
>>
>> On Fri, May 15, 2009 at 12:10 PM, Alin Popa <al...@gmail.com> wrote:
>>>
>>> Hi,
>>>
>>> I'm using iBatis with MySQL and also trying to do some pagination:
>>>
>>>
>>> return getSqlMapClientTemplate().queryForList("getAll", skipResults,
>>> maxRecords);
>>>
>>> and the "getAll" query:
>>>
>>> <select id="getAllVJobs" resultMap="simpleResult">
>>> SELECT * FROM mytable
>>> </select>
>>>
>>> Indeed, the pagination seems to work fine, BUT in mysql logs I see
>>> executed the entire "SELECT * FROM mytable" which is a full table
>>> scan.
>>> Doesn't seems very right to me.
>>>
>>> Is there a catch behind this idea ?
>>> Is fetching all records and after that iterate through them ? (I don't
>>> think it's very nice this way).
>>>
>>> Any ideas ?
>>>
>>> Thanks.
>>> Alin
>>
>>
>
>
>
> --
> Best Regards,
>
> Alin
>
Re: iBatis - MySQL pagination
Posted by Alin Popa <al...@gmail.com>.
Bhaarat,
If you're using mysql, you could do pagination using LIMIT statement,
which is pretty straight forward.
On Mssql - TOP (using cursors).
On Oracle - ROWNUM.
On Fri, May 15, 2009 at 7:14 PM, Bhaarat Sharma <bh...@gmail.com> wrote:
> that is a very interesting question. We were looking into doing pagination
> with iBatis as well but would not want a full scan on the DB but rather only
> get amount of rows specified by skipResults
>
> On Fri, May 15, 2009 at 12:10 PM, Alin Popa <al...@gmail.com> wrote:
>>
>> Hi,
>>
>> I'm using iBatis with MySQL and also trying to do some pagination:
>>
>>
>> return getSqlMapClientTemplate().queryForList("getAll", skipResults,
>> maxRecords);
>>
>> and the "getAll" query:
>>
>> <select id="getAllVJobs" resultMap="simpleResult">
>> SELECT * FROM mytable
>> </select>
>>
>> Indeed, the pagination seems to work fine, BUT in mysql logs I see
>> executed the entire "SELECT * FROM mytable" which is a full table
>> scan.
>> Doesn't seems very right to me.
>>
>> Is there a catch behind this idea ?
>> Is fetching all records and after that iterate through them ? (I don't
>> think it's very nice this way).
>>
>> Any ideas ?
>>
>> Thanks.
>> Alin
>
>
--
Best Regards,
Alin
Re: iBatis - MySQL pagination
Posted by Bhaarat Sharma <bh...@gmail.com>.
that is a very interesting question. We were looking into doing pagination
with iBatis as well but would not want a full scan on the DB but rather only
get amount of rows specified by skipResults
On Fri, May 15, 2009 at 12:10 PM, Alin Popa <al...@gmail.com> wrote:
> Hi,
>
> I'm using iBatis with MySQL and also trying to do some pagination:
>
>
> return getSqlMapClientTemplate().queryForList("getAll", skipResults,
> maxRecords);
>
> and the "getAll" query:
>
> <select id="getAllVJobs" resultMap="simpleResult">
> SELECT * FROM mytable
> </select>
>
> Indeed, the pagination seems to work fine, BUT in mysql logs I see
> executed the entire "SELECT * FROM mytable" which is a full table
> scan.
> Doesn't seems very right to me.
>
> Is there a catch behind this idea ?
> Is fetching all records and after that iterate through them ? (I don't
> think it's very nice this way).
>
> Any ideas ?
>
> Thanks.
> Alin
>