Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » EclipseLink and pagined queries
EclipseLink and pagined queries [message #1001292] Wed, 16 January 2013 12:08 Go to next message
Philippe is currently offline PhilippeFriend
Messages: 100
Registered: July 2009
Senior Member
Hello,

Is a way to perform an efficient pagined query with the mysql platform
(InnoDb driver) ?

My queries use
- the setMaxResults / setFirstResult methods to limit the number of results.
- one or several indexes (BTREE).

After several tests, it seems to me that EclipseLink uses no index to
optimize the queries . Is EclipseLink run through the entire base?
Is the use of a cursor could make queries optimized ?

Other question: setMaxResults / setFirstResult and ScrollableCursor seem
limited to integer values. Is a way to perform my pagined queries on a very
large database?

--
Philippe Bastiani
Re: EclipseLink and pagined queries [message #1001360 is a reply to message #1001292] Wed, 16 January 2013 15:06 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
EclipseLink should use the LIMIT function when using the MySQLPlatform DatabasePlatform class and setting a max rows return value. You can verify the SQL EclipseLink generates for a query by enabling logging at FINE or Finest as described here:
http://wiki.eclipse.org/EclipseLink/Examples/JPA/Logging

If Limit is not being used, you may need to verify the MySQL DBplatform class be used by specifying <property name="eclipselink.target-database" value="MySQL"/> in your persistence.xml, and check the EclipseLink version is 2.2 or greater.

If this doesn't help, please you describe the query you are executing, the SQL generated, the target-platform specified and the EclipseLink version you are using.

Best Regards,
Chris
Re: EclipseLink and pagined queries [message #1003138 is a reply to message #1001360] Sun, 20 January 2013 10:53 Go to previous message
Philippe is currently offline PhilippeFriend
Messages: 100
Registered: July 2009
Senior Member
Chris thank you for your quick reply and sorry for the delay of these
thanks!

I'm using EclipseLink 2.2.3 with the Mysql 5.1.18... And, yes I've
activated the finest level of logs !

The 2 values seem properly binded into the LIMIT clause. But finally, I
think my benchmarks are not significant ... because my queries have many
joins! So, I need to rethink theses ones to help JPA or use native
queries...

And, what about my second question: What is the best approach to support
pagined queries in a big database (I need to save 1-2000000 of records per
day during a year) ?

Thanks in advance
--
Philippe
Previous Topic:Multi-threaded overwrites
Next Topic:JPA + Native interference
Goto Forum:
  


Current Time: Fri Dec 19 04:05:57 GMT 2014

Powered by FUDForum. Page generated in 0.02350 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software