Re: [eclipselink-users] A question of native query for pagination.

[Shashikant Kale <Shashikant.Kale@xxxxxxxxxxxxxxxx>]

Hi Tom,

Thanks. We are using ojdbc driver provided by Oracle. I am not clear how in this driver the required rows are fetched. Would it fetch all the records to the app server memory and then provide the records in the said window i.e. firstResult to maxRows? I am not getting how driver should handle it.

Thanks,
Shashi

-----Original Message-----
From: eclipselink-users-bounces@xxxxxxxxxxx [mailto:eclipselink-users-bounces@xxxxxxxxxxx] On Behalf Of Tom Ware
Sent: Monday, October 18, 2010 5:57 PM
To: EclipseLink User Discussions
Subject: Re: [eclipselink-users] A question of native query for pagination.

Hi Shashi,

   When you write a native query, we cannot change the SQL on you.  That means
that any hints that would force a change to the actual SQL string have to be
either ignored, or dealt with in a different way.

   For firstrow and max results, we use JDBC API.  As Chris mentions, we use
statement.setMaxRows() to set the max rows, and we adjust the pointer into the
result set to achieve the firstResult functionality.  The behavior or these two
operations will depend on the JDBC driver's functionality.

   Is it possible to run a JPQL or Criteria query instead.  That will give us
the ability to construct the SQL ourselves and, for many databases allow us to
include pagination information in the actual SQL.

   Otherwise, you will likely have to include your pagination information in the
Native SQL String you provide us with.

-Tom

Shashikant Kale wrote:
> Hello,
>
>
>
> I am using a native query to get a paginated data from the DB(Oracle).
> However I don't see the ROWNUM and FIRST_ROWS hint being added to the
> query. I referred to the discussion at
> http://dev.eclipse.org/mhonarc/lists/eclipselink-users/msg03084.html,
> however I am not clear about the below response from Chris
>
>
>
> "That is not entirely true. For maxRows, a native query or non-paginated
> query will use statement.setMaxRows(). So depending on how the driver
> implements this, it should not be sending the entire result set.
> FirstResult is used to jump to the first row on the returned resultset
> though."
>
> Would database still return the full results set to the driver on the
> application server and driver would then hand over only a page of data
> to the application code or the DB itself would provide only the page of
> data to the application?
>
>
>
> We have an application where database CPU is hitting 100% quickly and
> remains high for the user load. I am also working on tuning the queries,
> however want to even know if there is anything else I am missing here.
>
>
>
> Would appreciate any help.
>
>
>
> Thanks,
> Shashi
>
>
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users