Massive performance issue with setParameter() and DateTime* objects

[maks-rafalko]

Bug Report

Q	A
BC Break	yes/no
Version	2.7.2

Related doc PR to warn users: #8114

Summary

Using $qb->setParameter('date', new \DateTimeImmutable()) without specifying 3rd argument (the type) leads to massive performance issues in long running processes.

This is a follow up for #7527 (originally posted here #7471 (comment))

Current behavior

As it's stated in the official docs (binding parameters to query)

Calling setParameter() automatically infers which type you are setting as value. This works for integers, arrays of strings/integers, DateTime instances and for managed entities.

So you can just pass a datetime object and it will work:

$qb->setParameter('date', new \DateTimeImmutable())

However, this leads to a) search this metadata in a cache, it fails and then b) load class metadata for every parameter of date types (e.g. \DateTimeImmutable), and such metadata does not exist of course.

Performance impact

We have a consumer that continuously reads data from the queue and executes queries, passing datetime object to setParameter().

Consumer is able to handle ~250 messages per secong, producing thousands of queries per second when it is fresh / is just started.

However, after some time there is a massive performance degradation (from 250/s, 230/s, ... 100/s, ... and so on). And this is exactly because of all the code that is executed just to load metadata for \DateTimeImmutable class which never exist).

So there are thousands of cache misses, thrown but caught exceptions, attempts to load metadata.

After changing this

$qb->setParameter('date', new \DateTimeImmutable())

to this

use Doctrine\DBAL\Types\Types;

$qb->setParameter('date', new \DateTimeImmutable(), Types::DATE_IMMUTABLE)

we are no longer experiencing performance issues. After 30 minutes of working, consumer still handles ~250 records per second without degradation.

How to reproduce

Just create any entity with datetime / datetime_immutable mapped property.

$em->getRepository(User::class)->createQueryBuilder('u');

$qb->select('u')
        ->where('u.createdAt > :now')
        ->setParameter(
            'now',
            new \DateTimeImmutable()
            // ,Types::DATE_IMMUTABLE uncomment to fix performance issue
        )
        ->getQuery()
        ->getResult();

Expected behavior

Expected behavior would be to not try loading metadata for datetime objects, but return the type immediately somewhere here:

orm/lib/Doctrine/ORM/Query.php

Lines 411 to 413 in 8c259ea

	if ($parameter->typeWasSpecified()) {
	return [$parameter->getValue(), $parameter->getType()];
	}

before trying to load metadata.

At this point, $parameter already have datetime type inside:

[maks-rafalko]

For maintainers: seems like this should be marked as won't fix according to Marco's answers in #8115

[bastnic]

FYI, the root issue happens in doctrine/persistence, see doctrine/persistence#96