If this is an isolated query the best suggestion I can offer is to
execute a native query using the platform specific mechanism for your
database to limit the result set.
You could try a different database driver.
It should be relatively easy to switch to EclipseLink in Glassfish V2.
Is the application using any native Essentials code? What are the
barriers to switching?
--Gordon
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
>