Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » JPQL count query slow(JPQL count query is slow)
JPQL count query slow [message #1257209] Wed, 26 February 2014 07:14 Go to next message
David Sills is currently offline David Sills
Messages: 9
Registered: July 2009
Junior Member
All:

I am having trouble and while I see some mention of others with the same issue in Google, I see none on the forum. I'm hoping that's because there's a simple solution.

I'm working on a web application using EclipseLink and JPA. Every page that contains a search results page is slow, whereas every page containing no search results is fast. Putting in some basic logging to test where this is happening, it was a pleasant surprise to me to realize that the data fetching was being done quite quickly (typically 80 or so milliseconds, though it is almost linear with the number of records available, which is discouraging, as it means the best optimizations are not being performed). However, that's not the only way we are using EclipseLink.

Typically, when doing a search, I begin by getting the entire count of records that match the query parameters. I do this in the simplest way I know how, e.g.:

select count(o) from Model o

Then, if there are more than 0 records, I go back and perform a data fetch.

I do this in part because there is more to doing the data fetch, but also because I need the count anyway, as the requirement is to display something like "Records 1-10 of 95".

I'm working in a system without a lot of users and without large tables or tables with lots of records. The number of records returned does not appear to matter. Whether the request is the first the application makes or the 4000th, doesn't appear to matter.

The logging indicated that the creation of the count Query object was what was taking the time, almost always from 2.5-3 seconds (yes, seconds!). The actual execution of the count query was nearly instantaneous. In the creation of the Query object, the time was pretty much entirely used within the EclipseLink code.

Has anyone heard of a case like this or can anyone offer a helpful solution?

Thanks!

David Sills
Re: JPQL count query slow [message #1257572 is a reply to message #1257209] Wed, 26 February 2014 15:30 Go to previous message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1019
Registered: July 2009
Senior Member
This seems very high, but I do not know how high it is relative to what is going on in the application. If you want to track down what might be causing the slow down further, I would suggest using EclipseLink's profiler outlined here: https://www.eclipse.org/eclipselink/documentation/2.4/solutions/performance002.htm
With this, you should be able to tell what part of this query is taking longer than expected, and deal with it appropriately. It would be good to compare against your main query fetching the data, and with subsequent requests.

Turning on EclipseLink logging might also help find reasons for the slow down. Not saying this is the case here but I have seen it where the application would close all sessions at the end of a process, forcing the first query in the next process to log in and initialize the persistence unit - making that first query always seem slower.

Suggestions that might help without knowing what is causing the problem would be to try using named queries. This would allow some of the query processing to be done up front and be reused for subsequent executions. Some other suggestions are made here : http://java-persistence-performance.blogspot.com/2011/06/how-to-improve-jpa-performance-by-1825.html , particularly the ones under section "Optimization #11 - Other Optimizations", assuming statement caching is already being used - such as ensuring the context is clear of changes that might get processed when the query is first executed.

Best Regards
Chris
Previous Topic:Changing lazy attributes when shared cache disabled
Next Topic:SIGSEGV in org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.checkCacheForObject
Goto Forum:
  


Current Time: Wed Sep 17 15:36:31 GMT 2014

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

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