Re: Digest for mybatis-user@googlegroups.com - 1 update in 1 topic

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: Digest for mybatis-user@googlegroups.com - 1 update in 1 topic

Vladimir Alarcon
Well, obviously the query is run in a different way. The "limit {1} offset {2}" is SQL syntax. However, you are trying to use the built-in JDBC driver functionality. Those are two different things.

When the application runs it, MyBatis assumes the JDBC driver will filter out using the RowBounds. The JDBC driver may or may not implement this functionality. It seems to me it's either not implemented, or implemented in a non-optimal way. Maybe it's reading the whole resultset first, and then filtering out the rows. Who knows.

I would suggest, trying to make the JDBC driver work for you, if possible, since you may have a ton of queries like this one. Have you tried SCROLL_SENSITIVE in the result set?

Also, are you sorting the rows by any column(s)? If so, with this amount of rows, you should have the appropriate index, in the correct column(s) order (ASC, DESC).

Also, if the PostgreSQL JDBC driver you are using does not implement the bound correctly, you can try other driver. I remember PostreSQL has three (at least) JDBC drivers written by different teams. Try another one, and see.

Finally, if nothing else works, you'll need to add the "limit {1} offset {2}" functionality yourself as part of the SQL statement. Not pretty but it will sure work, and will be fast.

Cheers.


On Thu, Mar 8, 2018 at 4:58 PM, <[hidden email]> wrote:
Marco Ferretti <[hidden email]>: Mar 08 06:36AM -0800

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.
You received this digest because you're subscribed to updates for this group. You can change your settings on the group membership page.
To unsubscribe from this group and stop receiving emails from it send an email to [hidden email].

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Digest for mybatis-user@googlegroups.com - 1 update in 1 topic

Vladimir Alarcon
Looks like the pagination is in the MyBatis layer rather than JDBC. In any case is not SQL syntax, so your queries are still different.

Maybe someone with deeper understanding of the MyBatis pagination functionality can give more insight.

On Thu, Mar 8, 2018 at 5:34 PM, Vladimir Alarcon <[hidden email]> wrote:
Well, obviously the query is run in a different way. The "limit {1} offset {2}" is SQL syntax. However, you are trying to use the built-in JDBC driver functionality. Those are two different things.

When the application runs it, MyBatis assumes the JDBC driver will filter out using the RowBounds. The JDBC driver may or may not implement this functionality. It seems to me it's either not implemented, or implemented in a non-optimal way. Maybe it's reading the whole resultset first, and then filtering out the rows. Who knows.

I would suggest, trying to make the JDBC driver work for you, if possible, since you may have a ton of queries like this one. Have you tried SCROLL_SENSITIVE in the result set?

Also, are you sorting the rows by any column(s)? If so, with this amount of rows, you should have the appropriate index, in the correct column(s) order (ASC, DESC).

Also, if the PostgreSQL JDBC driver you are using does not implement the bound correctly, you can try other driver. I remember PostreSQL has three (at least) JDBC drivers written by different teams. Try another one, and see.

Finally, if nothing else works, you'll need to add the "limit {1} offset {2}" functionality yourself as part of the SQL statement. Not pretty but it will sure work, and will be fast.

Cheers.


On Thu, Mar 8, 2018 at 4:58 PM, <[hidden email]> wrote:
Marco Ferretti <[hidden email]>: Mar 08 06:36AM -0800

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.
You received this digest because you're subscribed to updates for this group. You can change your settings on the group membership page.
To unsubscribe from this group and stop receiving emails from it send an email to [hidden email].


--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Digest for mybatis-user@googlegroups.com - 1 update in 1 topic

Marco Ferretti
Hi Vladimir,

thanks for the time you took to reply.

I inspected myBatis code and read documentation and experiences around and it seems that mybatis does in memory pagination; if that's the case then it would be pretty useless for decently big data. Anyways, I have found out that using an interceptor I could modify the SQL on the fly (aka before it is actually executed): with this "trick" I was able to add the offset and limit within the SQL and now the performance is pretty good again ( 6 ms for a single page).

Regards,

Marco F

On Fri, Mar 9, 2018 at 2:35 AM, Vladimir Alarcon <[hidden email]> wrote:
Looks like the pagination is in the MyBatis layer rather than JDBC. In any case is not SQL syntax, so your queries are still different.

Maybe someone with deeper understanding of the MyBatis pagination functionality can give more insight.

On Thu, Mar 8, 2018 at 5:34 PM, Vladimir Alarcon <[hidden email]> wrote:
Well, obviously the query is run in a different way. The "limit {1} offset {2}" is SQL syntax. However, you are trying to use the built-in JDBC driver functionality. Those are two different things.

When the application runs it, MyBatis assumes the JDBC driver will filter out using the RowBounds. The JDBC driver may or may not implement this functionality. It seems to me it's either not implemented, or implemented in a non-optimal way. Maybe it's reading the whole resultset first, and then filtering out the rows. Who knows.

I would suggest, trying to make the JDBC driver work for you, if possible, since you may have a ton of queries like this one. Have you tried SCROLL_SENSITIVE in the result set?

Also, are you sorting the rows by any column(s)? If so, with this amount of rows, you should have the appropriate index, in the correct column(s) order (ASC, DESC).

Also, if the PostgreSQL JDBC driver you are using does not implement the bound correctly, you can try other driver. I remember PostreSQL has three (at least) JDBC drivers written by different teams. Try another one, and see.

Finally, if nothing else works, you'll need to add the "limit {1} offset {2}" functionality yourself as part of the SQL statement. Not pretty but it will sure work, and will be fast.

Cheers.


On Thu, Mar 8, 2018 at 4:58 PM, <[hidden email]> wrote:
Marco Ferretti <[hidden email]>: Mar 08 06:36AM -0800

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.
You received this digest because you're subscribed to updates for this group. You can change your settings on the group membership page.
To unsubscribe from this group and stop receiving emails from it send an email to [hidden email].


--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.



--
--
Marco Ferretti
facebooktwitterLinkedinWebsite  public key




--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.