Eclipse Community Forums: EclipseLink » EclipseLink and pagined queries

Help

Home

Home » Eclipse Projects » EclipseLink » EclipseLink and pagined queries

Show: Today's Messages :: Show Polls :: Message Navigator

EclipseLink and pagined queries [message #1001292]

Wed, 16 January 2013 07:08

Philippe
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

Report message to a moderator

Re: EclipseLink and pagined queries [message #1001360 is a reply to message #1001292]

Wed, 16 January 2013 10:06

Chris Delahunt
Messages: 863
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

Report message to a moderator

Re: EclipseLink and pagined queries [message #1003138 is a reply to message #1001360]

Sun, 20 January 2013 05:53

Philippe
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

Report message to a moderator

Previous Topic:	Multi-threaded overwrites
Next Topic:	ClassLoader problem using JPA with Native Eclipselink project file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 22 05:43:32 EDT 2013