problem with paging

John Mazzitelli mazz at redhat.com
Thu Oct 6 13:16:05 UTC 2011


I posted a blog on this:

http://management-platform.blogspot.com/2011/10/problem-with-paging-and-jpa.html

On 10/05/2011 08:28 PM, John Mazzitelli wrote:
> for the record, this problem also occurred with postgres.
>
> and adding ORDER BY fixes the problem on both :)
>
> Thanks for the tip.
>
> On 10/05/2011 07:04 PM, John Mazzitelli wrote:
>> ok, I'll try that
>>
>> On 10/05/2011 06:40 PM, Charles Crouch wrote:
>>> Are you running on Oracle?
>>>
>>> If so, then it sounds like we're running into a variation of the
>>> problem described here:
>>> http://www.jroller.com/sjivan/entry/hibernate_and_oracle_pagination_gotcha
>>>
>>> i.e. we're asking Oracle to page the results of a query which doesn't
>>> have consistent row ordering.
>>>
>>> Looking at the named query
>>> Configuration.QUERY_GET_PLUGIN_CONFIG_MAP_BY_GROUP_ID I'm not seeing
>>> any ordering at all, so oracle is free to return the rows in any
>>> order it wishes, each time we ask for a new page. Obviously during
>>> this process, if you have a sufficiently large result set, some
>>> results are never getting returned in any page, in their place we're
>>> getting rows which have been returned before, which then get eaten in
>>> the 'results' HashMap. So if you hit this issue you'll always end up
>>> with fewer rows being reported than expected.
>>>
>>> So I think we need to make sure that for every query we use with
>>> paging that it has an order by clause. I presume oracle is
>>> deterministic about sorting on columns with duplicate values in,
>>> otherwise we'll have to order by a unique field.
>>>
>>> In fact I just found a very succinct recommendation from
>>> http://community.jboss.org/wiki/Pagination: "Always put an order by
>>> clause when paging on Oracle: we have found that on some instances
>>> the records are resorted between one page fetch and the subsequent
>>> one if no order by is specified."
>>>
>>> Cheers
>>> Charles
>>>
>>> ----- Original Message -----
>>>> While fixing some perf issues and writing this unit test supporting
>>>> large groups, I came across the following bug, but I don't know
>>>> what's
>>>> going on. Someone enlighten me.
>>>>
>>>> First, here's some code in
>>>> ConfigurationManagerBean.getPersistedPluginConfigurationsForCompatibleGroup
>>>>
>>>> - notice it LOOKS like it wants to chunk the db load via paging, but
>>>> it
>>>> really doesn't:
>>>>
>>>> // Configurations are very expensive to load, so load 'em in
>>>> chunks to ease the strain on the DB.
>>>> PageControl pageControl = new PageControl(0, 20);
>>>> Query query =
>>>> entityManager.createNamedQuery(Configuration.QUERY_GET_PLUGIN_CONFIG_MAP_BY_GROUP_ID);
>>>>
>>>> query.setParameter("resourceGroupId",
>>>> compatibleGroup.getId());
>>>>
>>>> Map<Integer, Configuration> results = new HashMap<Integer,
>>>> Configuration>((int) count);
>>>> int rowsProcessed = 0;
>>>> while (true) {
>>>> List<Object[]> pagedResults = query.getResultList();
>>>>
>>>> if (pagedResults.size()<= 0) {
>>>> break;
>>>> }
>>>>
>>>> for (Object[] result : pagedResults) {
>>>> results.put((Integer) result[0], (Configuration)
>>>> result[1]);
>>>> }
>>>>
>>>> rowsProcessed += pagedResults.size();
>>>> if (rowsProcessed>= count) {
>>>> break;
>>>> }
>>>> pageControl.setPageNumber(pageControl.getPageNumber() +
>>>> 1);
>>>> // advance the page
>>>> PersistenceUtility.setDataPage(query, pageControl); //
>>>> retrieve one page at a time // MAZZ ASKS - WHY IS THIS HERE?!?!?
>>>> }
>>>> return results;
>>>>
>>>> Look at the line that sets the data page (see the "MAZZ ASKS"
>>>> comment).
>>>> This is wrong. It should be up at the top as the first line in the
>>>> while
>>>> loop (and in fact, the analogous method in this SLSB for resource
>>>> configuration DOES have it in that spot - its almost identical code,
>>>> the
>>>> pattern is the same, but that setDataPage in this method is in the
>>>> wrong
>>>> spot). So, in fact, this chunk of code does NOT do paging, it really
>>>> loads everything in one big chunk.
>>>>
>>>> OK, fine, no problem - I fix this to match the same way its done in
>>>> getPersistedResourceConfigurationsForCompatibleGroup. I step through
>>>> in
>>>> a debugger and, yup, it is chunking now. I see the paging happening
>>>> as
>>>> the while loop actually does its thing.
>>>>
>>>> BUT!! IT DOESN'T WORK. I'm getting bad data! In the original "broken"
>>>> state (where it didn't do paging), I was getting 1010 results (which
>>>> is
>>>> correct in my case). So even though it didn't chunk, it was still
>>>> correct - I got all the data correctly. But when I "fix" the code, I
>>>> don't get 1010 results - sometimes I got 850, sometimes 1004,
>>>> sometimes
>>>> 945 - its not consistent, but most times I don't get the full amount
>>>> of
>>>> results back! So the paging "works" but I don't get the proper data
>>>> back!
>>>>
>>>> So, there are two things:
>>>>
>>>> 1) why is paging not working? What could cause this?
>>>> 2) This must mean the other SLSB method
>>>> getPersistedResourceConfigurationsForCompatibleGroup is ALSO BROKEN
>>>> and
>>>> we don't know it (because it uses the "fixed" way to do the paging).
>>>> I
>>>> suspect we just haven't looked closely to notice. I noticed because I
>>>> now have a unit test that actually tests to make sure I get back the
>>>> proper number of rows back. Where else do we use this paging (that is
>>>> really broken)? This could be bad.
>>>>
>>>> Note that paging is only broken when I have large # rows (like over
>>>> 500). I don't see the problem for small number of rows (like 100 or
>>>> less).
>>>> _______________________________________________
>>>> rhq-devel mailing list
>>>> rhq-devel at lists.fedorahosted.org
>>>> https://fedorahosted.org/mailman/listinfo/rhq-devel
>>>>
>>> _______________________________________________
>>> rhq-devel mailing list
>>> rhq-devel at lists.fedorahosted.org
>>> https://fedorahosted.org/mailman/listinfo/rhq-devel
>> _______________________________________________
>> rhq-devel mailing list
>> rhq-devel at lists.fedorahosted.org
>> https://fedorahosted.org/mailman/listinfo/rhq-devel


More information about the rhq-devel mailing list