Skip to main content

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] A question of native query for pagination.

Hello Shashi,

As described in the post you link, native queries use the JDBC statement setMaxRows() api to limit results.  How it works is an implementation detail of the driver - you will need to check your driver to see if it is actually limiting the results it is sending over the network or not.  In theory, it should limit the results sent over the network, so that only 0-max rows get sent to the resultset returned to EclipseLink.  EclipseLink will then use the FirstResult value to jump to a value in the resultset and use it to build FirstResult ->maxRow objects in memory. 

The alternative on oracle is to use the rowid to limit results on the database.  A feature exists to allow rowid to be built into JPQL queries, since EclipseLink creates the query.  The net result is that the SQL used itself limits the rows sent across the network to the FirstResult ->maxRow.  It is more efficient network and memory wise if FirstResult is sigificantly higher, and depending on how the driver behaves with setMaxRows.  If you want to take advantage of this in native SQL, you will need to format your SQL in the same way and not use the maxRow/firstResult api on the query object - allowing the database to do the filtering instead. 

Best Regards,
Chris

On 18/10/2010 9:22 AM, 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
  

Back to the top