problem with paging

Charles Crouch ccrouch at redhat.com
Thu Oct 6 00:52:27 UTC 2011


so how many other queries does this impact?

----- Original Message -----
> 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
> _______________________________________________
> 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