Skip navigation
simon135
Currently Being Moderated

Query result pagination performance

Apr 2, 2012 4:22 AM

Hi

 

I have CQ5.4 code (extract below) which uses QueryBuilder to create a query.  The result set is quite large (~2000 nodes) and ordered. I set the hits per page and the start page as I display the results using a pager.

 

Performance is good for the first page of results, but performance degrades quite significantly as the page start value is increased towards the end of the result set.  I find this strange as all nodes must always be accessed as the result set is sorted.

 

Does anyone have suggestions as to how I might resolve this performance issue?

 

Thanks

Simon

 

 

 

     QueryBuilder queryBuilder = resource.getResourceResolver().adaptTo(QueryBuilder.class);

 

     Session session = resource.getResourceResolver().adaptTo(Session.class);

     Query query = queryBuilder.createQuery(PredicateGroup.create(map), session);

     if (query != null) {

         int hitLimit = (pageMaximum>0) ? pageMaximum : limit;

         if (limit>0 && hitLimit>limit)

             hitLimit = limit;

         query.setHitsPerPage(hitLimit);

         query.setStart(pageStart);

         SearchResult result = query.getResult();

         totalMatches = result.getTotalMatches();

         actLimit = (limit>0) ? Math.min(limit, (int)totalMatches) : (int)totalMatches;

     }

 
Replies
  • Currently Being Moderated
    Apr 18, 2012 5:48 PM   in reply to simon135

    No idea why, do you have more information, like the actual query?

     
    |
    Mark as:
  • Currently Being Moderated
    May 2, 2012 5:22 AM   in reply to simon135

    In all cases, the querybuilder query itself takes around 350 ms, which is good, but the entire result building takes longer, the higher the offset is. Based on this, it seems to me that the internal call to RowIterator.skip() on the JCR query api takes that long (it skips offset number of rows), as that's the only thing depending on offset (number of hits and hence building the query builder result list is stable).

     

    I would investigate what happens if you run this query in xpath directly and measure accordingly:

     

    import javax.jcr.*;

    import javax.jcr.query.*;

    //...

    String xpath = "//*[((jcr:content/@pathTags = 'portal' and jcr:content/@pathTags = 'de' and jcr:content/@pathTags = 'testing'))] order by jcr:content/@cq:lastModified descending"

    QueryManager qm = session.getWorkspace().getQueryManager();

    QueryResult result = qm.createQuery(xpath, javax.jcr.query.Query.XPATH).execute();

    RowIterator rowIter = rawQueryResult.getRows();

    // until now should be the same execution time (~350 ms)

     

    // measure this

    rowIter.skip(2625); // offset

     

    // and maybe this

    List<Node> nodes = new ArrayList<Node>();

    // limit

    for (long i =0; i < 25 && rows.hasNext(); i++) {

        Row row = rows.nextRow();

        nodes.add(row.getNode());

        log.debug("found: {}", row.getNode().getPath());

    }

     

     
    |
    Mark as:
  • Jeff Brown
    8 posts
    Mar 23, 2012
    Currently Being Moderated
    May 10, 2012 1:49 PM   in reply to simon135

    So adding a path to the XPATH was faster for you than the "//*" in the log files you posted earlier?  That is opposite of what I have seen myself, where removing the path showed improvement for large result sets.  Reasoning is explained in Question #1 from a Jackrabbit committer in this old post (but is still referenced on the Jackrabbit Wiki so I assume still relevant) http://markmail.org/message/uew5xeyuzdb7v6bv

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points