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

[Tom Ware <tom.ware@xxxxxxxxxx>]

Hi Shashi,

  I am not an expert in how Oracle JDBC works, but I believe the JDBC driver 
will run SQL to retrieve all of the rows from the DB and manipulate the result 
on the client side for MaxRows and FirstResult.  It is possible that if you are 
using the OCI driver, some of the processing for setMaxResults will occur on the 
db server side, but the thin driver does not get a chance to manipulate the data 
until it gets to the client side.

  Either way, because of the way we manipulate the cursor for firstResult, the 
results sets will get bigger and bigger as you get more pages into your results. 
 If this is what is causing your issue, your best bet is to either write the 
SQL so that it inherently limits the results (via either a hint in the SQL or 
through the selection criteria) or use JPQL or Criteria query so we can include 
it for you.

-Tom

Shashikant Kale wrote:
> 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
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users