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

[Shashikant Kale <Shashikant.Kale@xxxxxxxxxxxxxxxx>]

Hi Tom,

Thanks. It was difficult for us to implement the query in JPQL and also we wanted to use few Oracle specific features in the query. However now I am going to provide the hint for pagination right in the query. I will update further on this thread about my observations.

Thanks for your help,
Shashi

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

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