Doctrine 1.2 Nested Set is Really slow. How can I improve it.(NestedSet)

Question

I perform a simple query with Doctrine 1.2.

Here is the query.

$cat = Doctrine_Core::getTable('Model_Category')->find($CatID);
if( isset($cat) && $cat->getNode()->hasChildren())
            $this->view->CategoryTree = $cat->getNode()->getChildren();

Why this is so slow ?.

Anybody has a solution to get a better performance of it.
Thanks

Answer 1

When I benchmarked my app I found that using the Doctrine_Core::HYDRATE_ARRAY made a big difference. When only using in View this often makes sense.

If you need more complex nested sets it may be a better option to just use the Doctrine_Query.

What you want is probably a query something like this:

$query = Doctrine_Query::create();
$query->from('Model_Category cat')
    ->leftJoin('cat.Node n')
    ->leftJoin('n.Childre c')
    ->where('count(c.id) > 0')
    ->andWhere('cat.id = ?', $id);
$query->execute(array(1), Doctrine_Core::HYDRATE_ARRAY)

Xdebug profiling can be very helpful, if your using Ubuntu:

sudo apt-get install php5-xdebug

Then:

sudo gedit /etc/php5/apache2/conf.d/xdebug.ini

In xdebug.ini I have:

zend_extension=/usr/lib/php5/20090626+lfs/xdebug.so
xdebug.profiler_enable=on
xdebug.profiler_output_dir="/tmp/xdebug"
xdebug.profiler_output_name = "cachegrind.out.%H.%R.%t"

Remember to create the dir:

mkdir /tmp/xdebug

sudo chgrp www-data /tmp/xdebug

chmod 774 /tmp/xdebug

Then I use KCachegrind to look at the output, hope this helps