Pagination with postgresql, using rowbounds does not generate limit/offset

[Marco Ferretti]

Hi all,

I am facing a problem for which  I must confess, I can't find the root cause.

I have a mapper with a method that is supposed to retrieve a single page based on RowBounds that looks like this

    @Options(resultSetType=ResultSetType.SCROLL_INSENSITIVE)
    @Results({
        @Result(column="id", property="id", jdbcType=JdbcType.BIGINT, id=true),
        @Result(column="country", property="country", jdbcType=JdbcType.VARCHAR),
        @Result(column="area", property="area", jdbcType=JdbcType.VARCHAR),
        @Result(column="vessel", property="vessel", jdbcType=JdbcType.VARCHAR),
        @Result(column="year", property="year", jdbcType=JdbcType.SMALLINT),
        @Result(column="haul_number", property="haulNumber", jdbcType=JdbcType.NUMERIC),
        @Result(column="codend_closing", property="codendClosing", jdbcType=JdbcType.CHAR),
        @Result(column="partit", property="partit", jdbcType=JdbcType.CHAR),
        @Result(column="genus", property="genus", jdbcType=JdbcType.VARCHAR),
        @Result(column="species", property="species", jdbcType=JdbcType.VARCHAR),
        @Result(column="codlon", property="codlon", jdbcType=JdbcType.CHAR),
        @Result(column="pfrac", property="pfrac", jdbcType=JdbcType.NUMERIC),
        @Result(column="pechan", property="pechan", jdbcType=JdbcType.NUMERIC),
        @Result(column="sex", property="sex", jdbcType=JdbcType.CHAR),
        @Result(column="nbsex", property="nbsex", jdbcType=JdbcType.NUMERIC),
        @Result(column="length_class", property="lengthClass", jdbcType=JdbcType.NUMERIC),
        @Result(column="maturity", property="maturity", jdbcType=JdbcType.VARCHAR),
        @Result(column="nblon", property="nblon", jdbcType=JdbcType.NUMERIC),
        @Result(column="matsub", property="matsub", jdbcType=JdbcType.VARCHAR),
        @Result(column="tf", property="tf", jdbcType=JdbcType.VARCHAR),
        @Result(column="month", property="month", jdbcType=JdbcType.SMALLINT),
        @Result(column="day", property="day", jdbcType=JdbcType.SMALLINT),
        @Result(column="catfau", property="catfau", jdbcType=JdbcType.VARCHAR),
        @Result(column="upload_id", property="uploadId", jdbcType=JdbcType.SMALLINT)
    })
    List<med.database.model.TMeditsTc> selectPage(RowBounds bounds);

The table contains (at the moment) ~2560000 records and the average page size is 30.

After reading http://www.mybatis.org/mybatis-3/java-api.html# , considering I am using PostgreSQL JDBC, my expectation would have been that the generated query would include limit ${size} offset ${start} but the whole page load is unexpectedly slow: it takes 42232 milliseconds against 12 milliseconds is run from psql with limit and offset.
After enabling sql log I can see that the generated SQL is

15:19:35,242 DEBUG [selectPage:159] ==>  Preparing: select id, country, area, vessel, year, haul_number, codend_closing, partit, genus, species, codlon, pfrac, pechan, sex, nbsex, length_class, maturity, nblon, matsub, tf, month, day, catfau, upload_id from upload.t_medits_tc

 
while I would have expected something like

select id, country, area, vessel, year, haul_number, codend_closing, partit, genus, species, codlon, pfrac, pechan, sex, nbsex, length_class, maturity, nblon, matsub, tf, month, day, catfau, upload_id from upload.t_medits_tc limit {1} offset {2}

Could anybody help me to find a generalized (PostgreSQL) solution for the issue? I have more tables with lots of data and would need a "generic" solution.


Thanks in advance for any help you can provide.

Marco F.

[Kazuki Shimizu]

Hi Marco,

The RowBounds skip the specified position using JDBC driver's API. It says in MyBatis Document as follow:

==== Documentation begin

The RowBounds parameter causes MyBatis to skip the number of records specified, as well as limit the number of results returned to some number. The RowBounds class has a constructor to take both the offset and limit, and is otherwise immutable.

int offset = 100;
int limit = 25;
RowBounds rowBounds = new RowBounds(offset, limit);

Different drivers are able to achieve different levels of efficiency in this regard. For the best performance, use result set types of SCROLL_SENSITIVE or SCROLL_INSENSITIVE (in other words: not FORWARD_ONLY).

==== Documentation end

If you use the lmit/offset in your SQL, you should be passed the limit/offset as mapper method parameters as follow:

@Select("SELECT ... FROM ... WHERE ... LIMIT ${limit} OFFSET ${offset}")
List<med.database.model.TMeditsTc> selectPage(@Param("offset") offset, @Param("limit") int limit);

Thanks.

Best Regards,

Kazuki

[Marco Ferretti]

Hi Kazuki,

yes, that is a solution. Unfortunately, I got aware of the problem after more than a year that the application was in production thus this specific solution is not applicable to my particular situation as it would mean changing a bunch of interfaces.

The solution I have found is to use an interceptor to modify the generated SQL on the fly in case a RowBounds object is involved.This solution is a lot more suited to my case as I had to just add test cases for the Interceptor implementation and the rest of the application simply kept working as it was before... only with much better performance.

Regards,

Marco F.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

--

Marco Ferretti