Jump to content
Pixrael

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

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?

Share this post


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

Share this post


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

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...