Is it possible that querying WireArray is slower than querying the database?

[Pixrael]

Today I found out that querying WireArray is a lot slower than querying the database.

First I implemented the first piece of code, and after that I wanted to optimize it thinking that consulting the database only once and then searching in the Wirearray will be better, but I discover that this is not an optimization, instead it's slower 2/3 times!

$today = strtotime('today');
$month = strtotime('first day of this month');

/* DATABASE QUERY = FASTER */
$shipments_active = count($pages->find("template=page-shipment,ship_status=1"));
$shipments_today = count($pages->find("template=page-shipment,created>$today"));
$shipments_month = count($pages->find("template=page-shipment,created>$month"));

/* WIREARRAY QUERY = SLOWER */
$shipments = $pages->find("template=page-shipment");
$shipments_active = $shipments->find("ship_status=1")->count();
$shipments_today = $shipments->find("created>$today")->count();
$shipments_month = $shipments->find("created>$month")->count();

Is this normal? Is there something I'm doing wrong?

[dragan]

If you don't need the entire page arrays of $shipments, then only querying the counts will always be way faster. Also, try to use findMany() instead of find().

I just ran three queries on a dataset of ~1100 pages in Tracy, and got an average of ~34ms:

<?php

$sel_2 = $pages->findMany("parent=1041, template=project, created>$month, include=all")->count;
$sel_3 = $pages->findMany("parent=1041, template=project, created<$last2Years, include=all")->count;
$sel_4 = $pages->findMany("parent=1041, template=project, images.count>0, include=all")->count;
d($sel_2);
d($sel_3);
d($sel_4);

Using just find(), it was about 10x slower.

Or you can even replace find() with findIDs:

<?php

$sel_2 = $pages->findIDs("parent=1041, template=project, created>$month, include=all");
$sel_3 = $pages->findIDs("parent=1041, template=project, created<$last2Years, include=all");
$sel_4 = $pages->findIDs("parent=1041, template=project, images.count>0, include=all");
d(count($sel_2));
d(count($sel_3)); 
d(count($sel_4));

This takes it down to ~20ms, i.e. approx. 33% faster than findMany()->count

Of course, if you have a really huge amount of pages, then a real direct DB-query (avoiding PW's API) will always be the fastest option. Or try @bernhard's RockFinder module.

Edited September 23, 2020 by dragan
added findIDs + link to RockFinder

[Pixrael]

Yes, thank you very much @dragan, I actually ended up using the findIDs call, but that's to querying the database .. my question refers to WireArray, because I supposed it would be faster to request the pages once to DB and then do the search operations on that array.