Indecent number of queries with PHPCR/Jackalope Doctrine DBAL?

Question

I'm just start learing PHPCR with Doctrine and Jackalope DBAL implemnetation.

Using the Symfony 2 debug toolbar I can see that, for a simple form and with parent property selection, it takes 15 queries (... added for readability):

[2014-08-24 15:55:48] doctrine.DEBUG: SELECT ... FROM phpcr_workspaces ...
[2014-08-24 15:55:48] doctrine.DEBUG: SELECT ... FROM phpcr_namespaces
[2014-08-24 15:55:48] doctrine.DEBUG: SELECT ... FROM phpcr_type_nodes
[2014-08-24 15:55:48] doctrine.DEBUG: SELECT ... FROM phpcr_type_props ...
[2014-08-24 15:55:48] doctrine.DEBUG: SELECT ... FROM phpcr_type_childs ...
[2014-08-24 15:55:48] doctrine.DEBUG: SELECT ... FROM phpcr_type_nodes ...
[2014-08-24 15:55:48] doctrine.DEBUG: SELECT ... FROM phpcr_type_props ...
[2014-08-24 15:55:48] doctrine.DEBUG: SELECT ... FROM phpcr_type_childs ...
[2014-08-24 15:55:48] doctrine.DEBUG: SELECT ... FROM phpcr_nodes ...
[2014-08-24 15:55:48] doctrine.DEBUG: SELECT ... FROM phpcr_nodes ...
[2014-08-24 15:55:48] doctrine.DEBUG: SELECT ... FROM phpcr_nodes ...
[2014-08-24 15:55:48] doctrine.DEBUG: SELECT ... FROM phpcr_nodes ...
[2014-08-24 15:55:48] doctrine.DEBUG: SELECT ... FROM phpcr_nodes ...
[2014-08-24 15:55:48] doctrine.DEBUG: SELECT ... FROM phpcr_nodes ...
[2014-08-24 15:55:48] doctrine.DEBUG: SELECT ... FROM phpcr_nodes ...

I have just three nodes:

/
/root
/root/child

I've added a simple form (as explained here) to create a new document and set its parent:

$form = $this->createFormBuilder($doc = new Document(), ['required' => false])
    ->add('name', 'text')
    ->add('title', 'text')
    ->add('parent', 'phpcr_document', [
        'property' => 'id',
        'class'    => 'Acme\DemoBundle\Document\Document',
        'multiple' => false,
    ])
    ->add('content', 'textarea')
    ->add('submit', 'submit')
    ->getForm()
    ->handleRequest($request);

if ($form->isValid()) {
    // ...
}

return [('form' => $form->createView()];

How can I lower the number of queries?

Answer 1

There are several things to consider here:

1. nodes metadata is very cacheable, so I recommend that you enable the caching for the metadata
2. for nodes there is also caching however the entire cache is invalidated when any node write is done, which may or may not make sense depending on the application
3. we are currently working on improving some aspects with reading nodes both in PHPCR ODM and Jackalope Doctrine DBAL
4. in some cases it might make sense to increase the fetch depth (in PHPCR ODM for child collections you can also define a fetch depth), which will automatically read children up to the specified depth which can reduce queries considerably
5. finally, all queries (when traversing nodes by path or uuid) are PK lookups without joins, so they tend to be really fast (for example to read a single node including all of its unstructured data, except for binaries, no joins are needed)

That being said, we do expect that there is still room for improvement in Jackalope Doctrine DBAL for performance but so far we have mostly focused on adding new features. But for example cmf.symfony.com runs on SQLite without any reverse proxy but using the metadata and node cache supported by Jackalope Doctrine DBAL and provides quite acceptable performance.