chrizz Reply #1, 3 months, 3 weeks ago
hey there,
I'm trying to optimize my code a little bit and when I started logging runtimes, I recognized that my getCollection calls seem to take more time than expected in the front end. For testing I turned off all caching features.
First of all: I'm running MODx 2.2-pl2 on a Win 7 with xampp: PHP 5.3.1, MySQL: 5.1.41. I know that logging runtimes by this kind of usage is a little bit inexact but it's useful to get a overview of loading times:
So what I'm trying to do is to collect data from modResource with these criteria:
In sum there are 99 elements that should be collected. The above shown code takes about 0,8 - 1,3 seconds.
At least I want to use this query to sort the results by the parent's menuindex (realized with a leftJoin).
The raw SQL Query with fetching the data takes about 0.003s
My first idea: The leftJoin slows down the query extremly. But at least the "leftJoin" query takes just a little longer than the regular one (about 0.05s).
Btw: In the backend I use a query with 3 leftJoins and it's much faster. So I think it's some kind of front end issue I don't know about.
Second idea: 99 results slow down the performance. I used a limit(0,10) but the result was nearly the same.
Last idea: Ask someone who knows more about xpdo and performance
So all I want to do is collecting data sort by the parent's menuindex to show them on a page in the front end.
I hope somebody could give me a hint
thanks a lot!
Chris
I'm trying to optimize my code a little bit and when I started logging runtimes, I recognized that my getCollection calls seem to take more time than expected in the front end. For testing I turned off all caching features.
First of all: I'm running MODx 2.2-pl2 on a Win 7 with xampp: PHP 5.3.1, MySQL: 5.1.41. I know that logging runtimes by this kind of usage is a little bit inexact but it's useful to get a overview of loading times:
$start = microtime(true); ... code goes here... $runtime = round((microtime(true) - $start), 4); echo "SQL Query: ".$runtime." ==> count: ".count($res);
So what I'm trying to do is to collect data from modResource with these criteria:
$oCriteria = $modx->newQuery("modResource");
$oCriteria->where(array("published" => 1, "deleted" => 0, "hidemenu" => 0, "parent:IN" => array(29,30,31)));
$oCriteria->sortby("menuindex", "ASC");
$res = $modx->getCollection("modResource",$oCriteria);
In sum there are 99 elements that should be collected. The above shown code takes about 0,8 - 1,3 seconds.
At least I want to use this query to sort the results by the parent's menuindex (realized with a leftJoin).
$oCriteria = $modx->newQuery("modResource");
$oCriteria->leftJoin("modResource", "Subcategory", "Subcategory.id = modResource.parent");
$oCriteria->where(array("modResource.published" => 1, "modResource.deleted" => 0, "modResource.hidemenu" => 0, "Subcategory.parent" => 28));
$oCriteria->sortby("Subcategory.menuindex, modResource.menuindex", "ASC");
$res = $modx->getCollection("modResource",$oCriteria);
The raw SQL Query with fetching the data takes about 0.003s
$resource = $modx->query("SELECT * FROM modx_site_content WHERE
published = 1 AND
deleted = 0 AND
hidemenu = 0 AND
parent IN (29,30,31)
");
$res = $resource ->fetchAll(PDO::FETCH_ASSOC);
My first idea: The leftJoin slows down the query extremly. But at least the "leftJoin" query takes just a little longer than the regular one (about 0.05s).
Btw: In the backend I use a query with 3 leftJoins and it's much faster. So I think it's some kind of front end issue I don't know about.
Second idea: 99 results slow down the performance. I used a limit(0,10) but the result was nearly the same.
Last idea: Ask someone who knows more about xpdo and performance
So all I want to do is collecting data sort by the parent's menuindex to show them on a page in the front end.
I hope somebody could give me a hint
thanks a lot!
Chris