Mybatis-3: [Feedback] Why Rowbounds accept Integer, when we can limit using Long.?

[edwin]

ive met a case where the number of data on my tables moves very fast, and perhaps reached 2billion data soon
in that case i need to use Long as offset parameter on Rowbound class,
but somehow it is not supported, because Rowbound only accept integer on both of its parameter

any thought.?

thank you.

[kazuki43zoo]

Thanks for your feedback !

The MyBatis support two ways for moving a cursor to the offset position.

1. Use the ResultSet#absolute(int)
2. Skip cursor until offset position using ResultSet#next() on loop

Probably this limitation depends on JDBC API specification of ResultSet#absolute(int).
In this case(for big data), I think you should be consider to use the SQL feature (such as LIMIT/OFFSET phrase) instead of the RowBounds.

@edwin
Please post your question to the mailing list at the next time because the GitHub issue manage only bug reports and feature requests.

Thanks.

[edwin]

hi @kazuki43zoo
it's a feature request actually, for adding rowbound's parameter to Long, thats why i put it here instead of mailinglist.
perhaps iterate ResultSet#absolute(int) several times until reach desired resultset's Long positition

thank you

[kazuki43zoo]

Hi @edwin ,

I am concerned about performance problems using RowBounds for big ResultSet.

Following is response time on Embedded PostgreSQL when move to last page by limit = 100.

Note:
fetchSize : 1000

| Records | offset/limit SQL(millis) | RowBounds
-next- (millis) | RowBounds
-absolute- (millis) |
| ---- | ---: | ---: | ---: |
| 20 million | 4,833
(16%) | 25,304
(84%) | 33,200 |
| 10 million | 1,996
(16%) |10,303
(84%) | 6,300 |
| 5 million | 1,034
(19%) | 4,301
(81%) | 3,200 |
| 1 million | 198
(30%) | 475
(70%) | 300 |
| 0.5 million | 112
(35%) | 204
(65%) | 160 |
| 0.1 million | 50
(48%) | 55
(52%) | 30 |
| 10,000 | 42
(81%) | 8
(19%) | 5 |
| 5,000 | 35
(85%) | 6
(15%) | 5 |
| 1,000 | 45
(92%) | 4
(8%) | 4 |

Following is response time on Oracle when move to last page by limit = 100.

| Records | SQL(millis) | RowBounds(millis) |
| ---- | ---: | ---: |
| 20 million | 2,879
(19%) | 12,179
(81%) |

Probably, I think RowBounds is not suitable for big ResultSet.

What do you think ?

[edwin]

Ouch, what a painful performance by RowBounds @kazuki43zoo
never thought that it will be that slow

(random question)
in that case, what is the purpose of it anyway.?

[kazuki43zoo]

(random question)
in that case, what is the purpose of it anyway.?

I am sorry, I could not understood your question ... (because my english skill is poor)
What do you want to know ?

[edwin]

no worries @kazuki43zoo, english is not my strong point also,
what i mean is, if the performance of RowBounds is very slow, what is the purpose of creating it.?
isnt it better just remove it,
or create warnings on documentation perhaps, so people would aware about its perfomance

[kazuki43zoo]

what is the purpose of creating it.?

I think the RowBounds excels in being able to absorb SQL dialect (limit/offset, rownum, etc ...).
And there is case that performance is high rather than SQL depending on data size.
Therefore I think RowBounds should not removed.

create warnings on documentation

I agree with it.

@harawata What do you think ?

[harawata]

@harawata What do you think ?

Removing RowBounds is not an option as it breaks existing solutions.
Warning in the documentation sounds good.

@edwin ,
Please see the wiki page for how to change the documentation.

[wushp]

ありがとう！@kazuki43zoo