"setcontent" Performance

[technicallyerik]

Enhancement

I was digging around the Symfony debugger and noticed ~180 queries used to build my homepage.

Right now, using setcontent (or any of the Frontend controllers that use it) does a separate query for:

• List of content IDs that match the query
• A query to get all the bolt_field for each content_id
• An individual query for each bolt_field_translation

Additionally, if you use the taxonomies or related twig filters, additional queries are made.

Environment

Question	Answer
Relevant Bolt Version	4.0.0-rc.17
Install type	Composer install
PHP version	7.3.9
Web server	Apache

Suggestion

Always join the related tables, and then filter by any criteria in the query.

I hardcoded a test twig query for the listing part of my homepage:

        $qb = $om->createQueryBuilder();
        $qb->select(['c', 'f', 't'])
            ->from(Content::class, 'c')
            ->innerJoin('c.fields', 'f')
            ->innerJoin('f.translations', 't')
            ->where('c.contentType IN (:cts)')
            ->setParameter('cts', ['blog','tutorials','links','quotes'])
            ->andWhere('c.status = :status')
            ->setParameter('status', Statuses::PUBLISHED)
            ->orderBy('c.publishedAt', 'DESC');
        $query = $qb->getQuery();
        $paginator = new Pagerfanta(new DoctrineORMAdapter($query, true, true));
        $paginator->setMaxPerPage(20);
        $paginator->setCurrentPage(1);

And it reduced the queries and time from:

to:

(The remaining queries are additional setcontents I use for the header and footer that would additionally be reduced)

Comments

I noticed some work in progress (#488) as well as a future ticket for caching that might make this invalid (#162), but wanted to raise the issue for posterity.

[bobdenotter]

Hi @technicallyerik ,

That issue in #488 is being worked on occasionally, but it'll be a separate approach.. Caching would be an additional performance gain.

Or, to turn it around: Optimiing these queries as they are right now would be very valuable! If you have a chance, please turn this into a PR. :-)

[bobdenotter]

Hey @technicallyerik,

Not trying to breathe down your neck, but did you get a chance yet to look at this? I think it'd be a valuable improvement.

[technicallyerik]

Hey @bobdenotter, sorry for the silence on this. I worked on this a little after I posted this ticket and last weekend. While I was able to make the appropriate joins, the unnecessary queries were still being made. I'm not sure if it was something I'm not understanding or Doctrine, or something Pagerfanta is doing. I'll give it another try soon, at a minimum I can push my WIP branch to my repo to have someone else take a look.

[bobdenotter]

I'll give it another try soon, at a minimum I can push my WIP branch to my repo to have someone else take a look.

Yes, please do! ^_^

[stryxx]

I have a question. This performance test contains changes from my PR? :) If not the performance test should be done in future once again :)

[bobdenotter]

@stryxx This was undoubtedly for the 'regular' version. When yours is done, we'll surely compare it as well! :-)