Slow php array processing

Question

My question might be a bit vague, because I cannot quite figure it out.

I have a piece of PHP that tries to convert a mysql query result into an array "tree". I.e. arrays of arrays depending on the defined groups.

The code assumes that a column name would start with a double underscore __ to indicate grouping and the results will already be ordered by the grouping.

The code works , but in certain cases it slows down to unusable speeds. Cases which I would expect it to be fast. Only one grouping with only a few unique values and many items in each branch sometimes takes upto 30seconds. Where other cases with many layers of branches and many different values , it only takes 1 second. (The result set is usually around 20 000 rows)

So, my question I guess is simply, what is wrong with my code ? Where am messing up so bad that it would impact performance significantly.

P.S. I'm a relative php novice , so be gentle :)

Sorry, no code comments O_o

$encodable = array();
$rownum = 0;
$branch = null;
$row = null;
$first = true;
$NULL = null;
$result = mysql_query($value,$mysql);
error_log (date("F j, Y, g:i a")."\r\n",3,"debug.log");
if (gettype($result) == "resource")
{

    while($obj = mysql_fetch_object($result))
    {
        $newrow = true;
        $branch = &$encodable;
        $row = &$NULL;
        if (count($branch) > 0)
        {
            $row = &$branch[count($branch)-1];
        }

        foreach ($obj as $column => $value)
        {
            if ($column[0] == '_' && $column[1] == '_')
            {

                $gname = substr($column,2);
                if (isset($row[$gname]) && $row[$gname] == $value)
                {

                    $branch = &$row["b"];
                    $row = &$NULL;
                    if (count($branch) > 0)
                    {
                        $row = &$branch[count($branch)-1];
                    }
                }
                else
                {
                    $branch[] = array();
                    $row = &$branch[count($branch)-1];

                    $row[$gname] = $value;
                    $row["b"] = array();
                    $branch = &$row["b"];
                    $row = &$NULL;
                    if (count($branch) > 0)
                    {
                        $row = &$branch[count($branch)-1];
                    }
                }
            }
            else
            {

                if ($newrow)
                {
                    $branch[] = array();
                    $row = &$branch[count($branch)-1];
                    $newrow = false;
                }
                $row[$column] = $value;
            }
        }

        $rownum++;
    }
}
$encoded = json_encode($encodable);

EDIT: A sample output - the resulting arrays is converted to json. This small set is grouped by "av" , b is created by the code for each branche and then contains a list of the [hid , utd] records per AV.

[{"av":"eset nod","b":[{"hid":"3","utd":"1"}]},{"av":"None","b":[{"hid":"2","utd":"0"},{"hid":"4","utd":"0"},{"hid":"5","utd":"0"},{"hid":"1","utd":"0"}]}]

The actual sql result that produced this result is:

 +----------+-----+-----+
 | __av     | hid | utd |
 +----------+-----+-----+
 | eset nod |   3 |   1 |
 | None     |   2 |   0 |
 | None     |   4 |   0 |
 | None     |   5 |   0 |
 | None     |   1 |   0 |
 +----------+-----+-----+

Two remarks: Why are you using references (&) everywhere? And it would really help if you provided us with a short piece of the actual result from the query. WIthout that it is hard to understand what you are doing. — Erwin Moller, Jul 09 '12 at 12:22
Additionally try to profile your code with xdebug and let us know what you see. — Mahn, Jul 09 '12 at 13:10
I've added sample input/ output. I'm not onsite right now, so I dont have access to the (full) data. I'll try to get xdebug going when I'm onsite again. Although I've never used it before. Coming from c++ background, it made sense for me to use the references as "rowpointers" as a loop through the rows and columns. So that inserts can always happen against the $row variable doesnt matter in which array or sub-array I am. I would not know how to do it otherwise. — Strahd_za, Jul 09 '12 at 13:48

score 0 · Answer 1 · edited May 23 '17 at 10:09

Turns out its all the calls to count($branch). Apparently calling a function that doesnt expect a variable by reference like count , With a variable by reference , causes the function to make a Copy of the variable to operate on.

In my case arrays with thousands of elements. Which also explains why its the results with few (but large branches) are the ones that suffer the most.

See this thread: Why is calling a function (such as strlen, count etc) on a referenced value so slow?

Slow php array processing

1 Answers1