I wrote this function to take database output like from PDO's fetchAll(PDO::FETCH_ASSOC)
and turn it into a tree. For small datasets it works well. However, when I run it on an array of 54,000 elements, it takes around 65 seconds on an Intel 7700K. I was hoping it would take less than a second, though maybe that's unrealistic. I'm using PHP 8.1.8.
Why is this function so slow?
Is there a way to optimize this function?
<?php
function treeifyDbRows($inputArray, $branchRules)
{
$output = [];
if ($branchRules != [])
{
$ruleSet = $branchRules[0];
$masterRule = $ruleSet[0];
$branchParents = array_column($inputArray, $masterRule);
$branchParents = array_unique($branchParents);
foreach ($branchParents as $branchParent)
{
$branchRulesAgain = [];
foreach ($branchRules as $ruleSetAgain)
{
if ($ruleSetAgain != $ruleSet)
{
$branchRulesAgain[] = $ruleSetAgain;
}
}
$branch = [];
$branchChildren = array_filter($inputArray, function($e) use ($branchParent, $masterRule){return $e[$masterRule] == $branchParent;});
$branchChildren = array_values($branchChildren);
foreach ($ruleSet as $rule)
{
$branch[$rule] = $branchChildren[0][$rule];
}
$children = treeifyDbRows($branchChildren, $branchRulesAgain);
if ($children != [])
{
$branch['children'] = $children;
}
$output[] = $branch;
}
}
return $output;
}
$data =
[
[
"countryID" => 1,
"countryName" => "USA",
"regionID" => 10,
"regionName" => "Indiana",
"cityID" => 100,
"cityName"=> "Indianapolis",
],
[
"countryID" => 1,
"countryName" => "USA",
"regionID" => 10,
"regionName" => "Indiana",
"cityID" => 101,
"cityName"=> "Bloomington",
],
[
"countryID" => 1,
"countryName" => "USA",
"regionID" => 11,
"regionName" => "Michigan",
"cityID" => 102,
"cityName"=> "Lansing",
],
[
"countryID" => 2,
"countryName" => "Canada",
"regionID" => 12,
"regionName" => "Ontario",
"cityID" => 103,
"cityName"=> "Toronto",
],
[
"countryID" => 2,
"countryName" => "Canada",
"regionID" => 12,
"regionName" => "Ontario",
"cityID" => 104,
"cityName"=> "Ottawa",
],
[
"countryID" => 2,
"countryName" => "Canada",
"regionID" => 12,
"regionName" => "Ontario",
"cityID" => 105,
"cityName"=> "Windsor",
],
[
"countryID" => 2,
"countryName" => "Canada",
"regionID" => 25,
"regionName" => "Quebec",
"cityID" => 106,
"cityName"=> "Montreal",
],
];
$rules =
[
["countryID", "countryName"],
["regionID", "regionName"],
["cityID", "cityName"],
];
$results = treeifyDbRows($data, $rules);
var_dump($results);
ORDER BY
to the query analogous tocountryID
,regionID
,cityID
, bothASC
andDESC
, andtreeifyDbRows
took around 75 seconds to run. I don't know if that's actually slower, or if that's just experimental error. \$\endgroup\$array_
function calls will cause the creation of a new array and copying of the content element-by-element, followed by deallocation of the old one, which could be quite slow if it happens a lot or there's many elements in the array. You might want to try replacing those calls with a procedural approach where you make an empty array and then do a foreach loop adding elements to it with[] =
in a single pass. If you can avoid creating new arrays at all, even better. \$\endgroup\$