ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Clinton Begin <clinton.be...@gmail.com>
Subject Re: iBatis - MySQL pagination
Date Fri, 15 May 2009 18:46:03 GMT
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 <clinton.begin@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 <alin.popa@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
>>> <brandon.goodin@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 <bhaarat.s@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
>>>
>>
>>
>

Mime
View raw message