|JPQL count query slow [message #1257209]
||Wed, 26 February 2014 07:14
| David Sills
Registered: July 2009
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?
Powered by FUDForum
. Page generated in 0.03653 seconds