Hello,
One of our customers reported a performance issue.
The performance remarkably deteriorates when the large value is
specified for the argument of Query#setFirstResult().
Below are the execution time of retrieval result when specifying
different values of the argument.
The start position of the result : The execution time of retrieval result
setFirstResult(1) : 31 ms
setFirstResult(5000) : 297 ms
setFirstResult(50000) : 2875 ms
The result showed the execution time increased when the value of start
position grew.
Below is the result using Hibernate that shows there is no performance
deterioration occurred.
setFirstResult(1) : 16 ms
setFirstResult(5000) : 15 ms
setFirstResult(50000) : 16 ms
Assumed cause:
It seems that it takes much time on the processing of moving to a given
row of the ResultSet object by using java.sql.ResultSet.absolute.
Since the current platform they are using is GlassFish v2 b58 it is
difficult to switch to EclipseLink. Could you someone please suggest a
fix plan for this.
Regards,
Xiaojun