Jump to content

Improve performance of big query


Pete Jones
 Share

Recommended Posts

Hi, sorry to bring this up again. After looking further down the pages, we can see some improper ordering. We are getting pages that should be found with this selector

"template=horse-note, sort=-h_notes_last_comment"

appearing before pages from this selector

"template=horse-note, $session->unread_by>0, sort=-h_notes_last_comment"

 

Link to comment
Share on other sites

If you manipulate this method (the only one) in PagesPaginatorNoDuplicates.php you could log or otherwise store found pages/selectors for each separate part. The resulting list is just a pagearray, where new pages are appended one after another.

protected function getItems($selector, $key, $start, $limit, $storage)
{
	$selector = $selector . ", start=$start, limit=$limit, id!=$storage";
	wire('log')->save('paginator', $selector);
	$pa = wire('pages')->find($selector);
	wire('log')->save('paginator', (string) $pa);
	return $pa;
}

The class(es) itself do not order things. It could be that $start / $limit values are off, but these are tested, so they should work. Could also be that some selector of yours is not working as you expect it. 

  • Like 1
Link to comment
Share on other sites

Hi LK, we appear to have an issue with where the results are starting from on a particular page. For example, we're getting items on page 2 that have already been shown on page 1. Could this be something to do with the 'no duplicates' functionality that is somehow affecting start and end points?

Link to comment
Share on other sites

Ah yeah, sure. The items on page1 never make it into the $storage variable for page2, which I didn't really realize. So you'd probably need to make your selectors a bit more concrete, so they don't overlap. Otherwise we're back to loading at least all id's of all items.

Link to comment
Share on other sites

There is no $alreadySelectedElements, because we're not loading any items before those we really need. That's the crux in pagination. It should at best work without the runtime even knowing which items came before the current page, because otherwise there will always be scalability issues. 

  • Like 1
Link to comment
Share on other sites

Just stripping this back to basics so that I can prevent duplicates in my selectors and have noticed that the order is being controlled by the sort that I have in the selectors, rather than the order that the selectors are put into the $paginator function. That probably makes no sense, so I'll try to illustrate.

My paginator function looks like this:

$notes = $paginator(array(
	"template=horse-note, h_notes_comments.$session->unread_by>0, sort=-h_notes_last_comment",
	"template=horse-note, $session->unread_by>0, sort=-h_notes_last_comment"
), $pageNum, $limit);

h_notes_comments is a repeater with a few text fields and some checkboxes.

So what I should get is:

  • pages with 1 or more of the h_notes_comments repeaters containing a checked checkbox, sorted by the h_notes_last_comment datetime field
  • other pages without a checked checkbox inside the h_notes_comments repeater (or no repeater items at all), sorted by the h_notes_last_comment datetime field

But what's happening is the h_notes_last_comment field is taking precedence, so pages found with the second selector are appearing before pages found with the first, if the h_notes_last_comment happens to be more recent. Is that the expected behaviour or are we doing something wrong in our selectors?

As an aside, am i right in thinking that when we're doing

h_notes_comments.$session->unread_by>0

that will match any page that has at least one of the h_notes_last_comments repeaters with that checkbox checked? So even if there are 8 repeater items on a page, and 7 of them don't have the checkbox checked, it will still be returned?

Link to comment
Share on other sites

You should really test your selectors first before packing it into the paginator and trying to make sense of it. But I finally managed to setup a small test case and found a few issues, not releated to sorting, but to counting the total number of pages. It seems like there's no possible solution to wrap multiple selectors, where at least one does use or-groups, to one combined to run a $pages->count() on. 

  • Like 1
Link to comment
Share on other sites

Yes, I have noticed that renderPager() is giving more pages than needed. Before I look at that, there is a strange reordering happening. Take this pair of selectors:

$notes = $paginator(array(
		"template=horse-note, parent.h_type=2, h_notes_comments.count>0, h_notes_comments.$session->unread_by>0, sort=-h_notes_last_comment",
		"template=horse-note, parent.h_type=2, $session->unread_by>0, sort=-h_notes_last_comment"
	), $pageNum, $limit);

One of the pages from the second selector is appearing first in the list. It's definitely coming from that selector, because if I remove that from the array, that page is no longer returned. All other pages seem to be in the correct order.

I've also noticed that on page 1, where I am echoing out count($notes), this is returning 96 even though $limit is 100. It's fine on all subsequent pages though.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...