Hi Xiaojun,
This issue is solved for Oracle, MySQL and PostGreSQL on EclipseLink.
It is not clear to me why switching to EclipseLink would be difficult since it
is just a matter of getting eclipselink.jar, switching persistence providers and
renaming any proprietary API used (probably very little)
If that is a problem, the other option is to take a look at the source code
for OraclePlatform, MySQLPlatform or PostGreSQLPlatform in EclipseLink and build
a subclass of the platform that is currently being used with the appropriate
changes in Essentials.
-Tom
Xiaojun Zhang wrote:
> 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