bigger..... and slower.

[muzzer]

I have what is building to be what I would consider a large site and am starting to notice a few pages running slower than I would like. I've been looking at various options for speeding them up and it seems the database interactions are probably the part which drag the most.

So now I'm trying to understand how PW handles queries and pages objects so I can program more efficiently, so my question is:

 

$bookings = $pages->get('/bookings/')->find('room=roomOne');

foreach($bookings as $booking)
	$roomData = $booking->find('date=' . $today);

 

In the above snippet is the third line performing the find() on the $bookings pages object in memory or is it querying the database again and using the $bookings ojects to refine the query?

I'm trying to reduce the database queries (especially repeat queries) so understanding this would be helpful.

 

[Robin S]

I believe that each time you call $pages->find() or $page->find() the PageFinder class is used to query the database.

If you're wanting to analyse performance in detail then Profiler Pro in the new ProDevTools would probably be money well spent. Besides the tool itself there is this benefit:

Quote

When you purchase ProDevTools, you are purchasing full access to the VIP ProDevTools member area. This is where downloads and upgrades for products are made available, and where VIP support is provided directly by ProcessWire's lead developer, Ryan. 

This is particularly useful with the ProfilerPro service, and Ryan is there to help you optimize your site and resolve bottlenecks discovered with ProfilerPro.

 

[teppo]

29 minutes ago, muzzer said:

In the above snippet is the third line performing the find() on the $bookings pages object in memory or is it querying the database again and using the $bookings ojects to refine the query?

Database. If it was an in-memory query, the first find() operation would need to return every single child page along with the pages actually matching the query.

I don't know anything about your structure, but it appears that you have "room pages" below /bookings/ and each room page has a variable number of booking pages as it's direct children, right? In the first query, if you're only interested in the direct children of /bookings/, you should use children() instead of find(). You should get a notable speed bump from this alone.

Additionally: while this is probably not production code, please note that with every find() operation that has the potential to match a large number of results you should use limit, or otherwise you'll risk running into both performance and memory issues at some point.

[LostKobrakai]

In memory search is only possible if the to be searched element is already retrieved, which means only if you call find on a PageArray. You're calling find on a Page object, which is just syntax sugar for $pages->find("parent=$booking, …") .

 

34 minutes ago, muzzer said:

… it seems the database interactions are probably the part which drag the most.

I'd suggest you using https://blackfire.io/ for profiling. It's the best tool to look into the system I've ever used. With few parts of ProcessWire being called all over the place it requires a bit searching to find the exact part of code you actually can improve on, but it's worth for not needing to guess.

To improve on plain query count (not necessarily speed) you go this way:

$bookings = $pages->get('/bookings/')->find('room=roomOne'); // 2 queries

foreach($bookings as $booking)
	$roomData = $booking->find('date=' . $today); // n queries

// Improved
$bookings = $pages->find('parent=/bookings/, room=roomOne'); // 1 query
$roomsData = $pages->find("parent=$bookings, date=$today"); // 1 query

foreach($bookings as $booking)
	$roomData = $roomsData->find("parent=$booking");

Edit:

Just to add this here. Doing database queries in foreach loops is almost always to be avoided.

[Robin S]

I expect this wouldn't suit because you want the separate $bookings variable for doing other things with, but just wanted to add that you can get to an array of room data in a single query:

$roomsData = $pages->find("date=$today, parent.room=roomOne"); // you'd probably limit by template here too