Open edwin opened 7 years ago
Thanks for your feedback !
The MyBatis support two ways for moving a cursor to the offset position.
ResultSet#absolute(int)
ResultSet#next()
on loopProbably 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.
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
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 ?
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.?
(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 ?
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
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 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.
ありがとう!@kazuki43zoo
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.