Jump to content

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


Pixrael
 Share

Recommended Posts

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?

Link to comment
Share on other sites

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 by dragan
added findIDs + link to RockFinder
  • Like 1
Link to comment
Share on other sites

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.

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

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...