Jump to content
psy

[solved] How to speed up DB queries

Recommended Posts

I have a $pages selector that works great in giving me the requested info. However it's incredibly slow. Added to this, the request may be called up to 15 times on a particular page for 15 different listed items.

<?php
	// in my custom module...
	// retrieves qty of items already hired on a particular day to determine availability for new bookings
	// booking_items is a repeater field and booking_item are repeater pages
	// I already have the $item and the $eventDate
	// Query is searching through 5K+ bookings and at least 3x that many booking_item repeater pages

        $limit = $item->qty;
        $selector = "template=booking, booking_items.booking_item.id=$item, booking_date=$eventDate, booking_status!=pending|cancelled, limit=$limit";
        $pp = $this->wire()->pages;
        $otherBookings = $pp->find($selector);

Each item query can take between 2 to 8 seconds 😬

What can I do to speed up this query?

TIA

psy

Share this post


Link to post
Share on other sites

If you really need some serious speed increase you can of course go directly to the database instead of using $pages. Though that is a lot more work, because you'll have to manually add status flags to your query, manage multilanguage support etc. I've done this for my module Automatically link page titles, the list of linkable titles and corresponding page IDs is retrieved directly from the field_title table. See the source code for reference.

If you can't optimize the query, caching might be a solution, this way only the initial access will run slowly, but subsequent requests and queries will be fast. Your query varies by $item, $eventDate and $limit, so you could create a cache key based on that and store the results through the $cache API. Something like this:

$limit = $item->qty;
$cacheKey = implode('-', ['bookings', $item, $eventDate, $limit]);
$cache = $this->wire('cache');
$selector = "template=booking, booking_items.booking_item.id=$item, booking_date=$eventDate, booking_status!=pending|cancelled, limit=$limit";
$pp = $this->wire()->pages;
$otherBookings = $cache->get(
	$cacheKey,
	WireCache::expireSave,
	function () use ($pp, $selector) {
		return $pp->find($selector);
	}
)

Quick and untested, might need some adjustments. You can use WireCache::expireSave to invalidate the cached results whenever any page is saved, though that's not very efficient if the bookings are edited very frequently. Or use a max age of a day or so, at the risk of sometimes showing outdated results.

  • Like 4

Share this post


Link to post
Share on other sites

Might RockFinder speed things up sufficiently? Latest version at https://github.com/baumrock/rockfinder3.

I'd expect it to reduce your query times greatly - probably well under a second. And you can keep using PW selectors.

  • Like 1

Share this post


Link to post
Share on other sites
8 hours ago, psy said:

retrieves qty of items

Can you elaborate on this? Is the quantity derived from a page / selector count? And that's all you really need with all those queries? There are faster ways to get counts than to always return the full page arrays. (I probably miss the "big picture", sorry if it's a dumb question)

Share this post


Link to post
Share on other sites

@BillH Thanks,

7 hours ago, BillH said:

Might RockFinder speed things up sufficiently?

I tried RockFinder3 a while ago. It's certainly a great module however I couldn't figure out how to get it working with Repeater fields. It's perfect with direct relation fields, eg PageReference but Repeaters are different beasts. Not sure if RF3 handles '.' separated fields in the selector, eg "booking_items.booking_item.id" either?

Share this post


Link to post
Share on other sites

@dragan

No question you ask is ever dumb! I should have made it clearer.

Each item is a page, a product page. The qty is an integer field and refers to how many units of that product are owned by the client. The $limit is set to that number.

The query searches through other bookings for that item on the nominated day, and on each match, subtracts the booking_qty from the item qty, to return how many are left for hire on that date. When all are booked out, the front-end customer cannot add it to their cart to prevent over-booking.

A count wont do it as other bookings may have booked 2 or more units of the same item on that day.

Share this post


Link to post
Share on other sites

Grateful for all your suggestions and you gave me an idea. Didn't get a chance to test today but will try tomorrow. It's a conglomeration of your suggestions.

  1. in _init.php prepended to all pages (or maybe refined to only the pages on which it's needed ?), collect all bookings for the day, regardless of individual item ids and cache it. Historical bookings won't change and without the repeater stuff, will be quick;
  2. instead of going back to the db for all calls, run the individual item queries on the cached page array - at most 20 items

Will report back 🙂 

Share this post


Link to post
Share on other sites

Here's another...

I am not sure if in your case the slowness is due to the query itself or the page objects that have to be created after the find. If the latter, you can tell ProcessWire not to return page objects. You can then work with arrays as it seems your main interest are not the items themselves but their IDs and the quantities. 

Just a quick thought..

  • Like 1

Share this post


Link to post
Share on other sites

Just tested and it works a treat 🙂

When a frontend user enters their event date, in _init.php:

<?php

if (!empty($input->post->availability)) {
    $availability = $sanitizer->date($input->post->availability, 'Y-m-d');
    $sameDayBookings = $pages->find("template=booking, booking_date=$availability, booking_status!=pending|cancelled",['loadOptions'=>['autojoin' => true, 'joinFields' => ['booking_items']]]);
    $cache->save('sameDayBookings', $sameDayBookings);
}

This query takes no time at all. Next, in my custom module:

<?php

    /**
     * @param Page $item - product page
     * @return int|void
     * @throws WireException
     */
    public function itemAvailability(Page $item)
    {
        if ($item instanceof NullPage) return;

        $itemBookingCount = 0;
        $otherBookings = $this->wire('cache')->get('sameDayBookings');

        if ($otherBookings->count > 0) { // there are other booking on the same event date
            foreach ($otherBookings as $otherBooking) {
                foreach ($otherBooking->booking_items as $otherBookingItem) {
                    if ($otherBookingItem->booking_item->id == $item->id) {
                        $itemBookingCount = (int)$itemBookingCount + (int)$otherBookingItem->booking_qty;
                    }
                }
            }
        }

        $itemsAvailable = (int)$item->qty - (int)$itemBookingCount;
        return $itemsAvailable;
    }

Query times for this function went from an average of 3.5sec to under 0.01 seconds per item and under 1sec to render the page listing multiple items with their availability. Happy dance time!

Thank you all!

 

  • Like 3

Share this post


Link to post
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

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...