Why Test SQL Query Counts?#

Poor SQL query management can lead to significant slowdowns, especially when Lazy-Loading is misused. This test helps to:

• Identify pages where too many queries are executed.
• Prevent the N+1 problem: Doctrine executes an additional query for each related entity instead of using an optimized SQL join.

Prerequisites#

Before starting, you need a Symfony project with a functional database.

Create the project:

symfony new BlogTestsProfiler --webapp

Configure the database:

DATABASE_URL="sqlite:///%kernel.project_dir%/var/data.db"

Setting Up Entities#

Here are the Author and Post entities:

#[ORM\Entity(repositoryClass: AuthorRepository::class)]
class Author
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;

    #[ORM\Column(length: 255)]
    private ?string $username = null;

    #[ORM\OneToMany(targetEntity: Post::class, mappedBy: 'author')]
    private Collection $posts;
}

#[ORM\Entity(repositoryClass: PostRepository::class)]
class Post
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;

    #[ORM\Column(length: 255)]
    private ?string $title = null;

    #[ORM\Column(type: Types::TEXT, nullable: true)]
    private ?string $content = null;

    #[ORM\ManyToOne(inversedBy: 'posts')]
    #[ORM\JoinColumn(nullable: false)]
    private ?Author $author = null;
}

Generating Data with Faker#

Set up fixtures:

composer require orm-fixtures --dev
bin/console make:fixtures

Install Faker to generate dummy data:

composer req fakerphp/faker --dev

Example fixture:

class AppFixtures extends Fixture
{
    public function load(ObjectManager $manager): void
    {
        $faker = \Faker\Factory::create();

        for ($i = 0; $i < 100; $i++) {
            $author = new Author();
            $author->setUsername($faker->userName);
            $manager->persist($author);

            for ($j = 0; $j < rand(0, 100); $j++) {
                $post = new Post();
                $post->setTitle($faker->sentence);
                $post->setContent($faker->paragraph);
                $post->setAuthor($author);
                $manager->persist($post);
            }
        }

        $manager->flush();
    }
}

Create, migrate, and load data:

bin/console doctrine:database:create
bin/console make:migration
bin/console doctrine:migration:migrate
bin/console doctrine:fixtures:load

Creating the Controller and View#

#[Route('/', name: 'app_author')]
public function index(EntityManagerInterface $entityManager): Response
{
    $authors = $entityManager->getRepository(Author::class)->findAll();

    return $this->render('author/index.html.twig', [
        'authors' => $authors,
    ]);
}

{% extends 'base.html.twig' %}

{% block title %}Authors{% endblock %}

{% block body %}
<table>
    <thead>
        <tr>
            <th>Id</th>
            <th>Name</th>
            <th>Nb Posts</th>
        </tr>
    </thead>
    <tbody>
        {% for author in authors %}
            <tr>
                <td>{{ author.id }}</td>
                <td>{{ author.username }}</td>
                <td>{{ author.posts|length }}</td>
            </tr>
        {% endfor %}
    </tbody>
</table>
{% endblock %}

Navigating to the controller’s route already shows too many queries:

Enabling the Profiler in Test Environment#

By default, Symfony’s profiler is not enabled in the test environment. To allow PHPUnit to collect SQL query information, modify the config/packages/framework.yaml file as follows:

when@test:
    framework:
        test: true
        profiler:
            enabled: true
            collect: false

This enables the profiler while disabling automatic data collection, which can be resource-intensive. The test will manually activate the profiler for each simulated request.

Writing the PHPUnit Test#

class RequestCountTest extends WebTestCase
{
    public function testRequestCount(): void
    {
        $client = static::createClient();
        $client->enableProfiler();

        $crawler = $client->request('GET', '/');
        $this->assertResponseIsSuccessful();

        $this->assertLessThan(10, $client->getProfile()->getCollector('db')->getQueryCount());
    }
}

Running and Analyzing Results#

Run the test with the command:

bin/phpunit

The test should fail, indicating that the number of executed SQL queries exceeds the defined limit:

Failed asserting that 101 is less than 10.

Fixing the Issue#

• Set fetch = EAGER:

#[ORM\OneToMany(targetEntity: Post::class, mappedBy: 'author', fetch: 'EAGER')]
private Collection $posts;

• Use a DQL Query with Joins:

$authors = $entityManager->createQueryBuilder()
    ->select('a', 'p')
    ->from(Author::class, 'a')
    ->leftJoin('a.posts', 'p')
    ->getQuery()
    ->getResult();

I prefer the second solution as it provides more control over queries. Using fetch = EAGER can be discouraged for large data volumes due to high memory consumption.

Re-running the test will show it passing, and you can confirm it in the profiler:

Conclusion#

By testing SQL query counts and fixing N+1 issues, you ensure optimal performance for your Symfony application.

$this->assertLessThan(
    500,
    $profile->getCollector('time')->getDuration()
);