Massive performance issue with setParameter() and DateTime* objects #6450

New Issue

admin · 2026-01-22T15:33:28+01:00

admin commented

2026-01-22 15:33:28 +01:00

Originally created by @maks-rafalko on GitHub (Apr 17, 2020).

Bug Report

Q	A
BC Break	yes/no
Version	2.7.2

Related doc PR to warn users: https://github.com/doctrine/orm/pull/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 https://github.com/doctrine/orm/issues/7527 (originally posted here https://github.com/doctrine/orm/pull/7471#discussion_r241949045)

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:

8c259ea5cb/lib/Doctrine/ORM/Query.php (L411-L413)

before trying to load metadata.

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

Originally created by @maks-rafalko on GitHub (Apr 17, 2020). ### Bug Report  | Q | A |------------ | ------ | BC Break | yes/no | Version | 2.7.2 Related doc PR to warn users: https://github.com/doctrine/orm/pull/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 https://github.com/doctrine/orm/issues/7527 (originally posted here https://github.com/doctrine/orm/pull/7471#discussion_r241949045) #### Current behavior As it's stated in the official docs ([binding parameters to query](https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/reference/query-builder.html#binding-parameters-to-your-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: ```php $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 ```php $qb->setParameter('date', new \DateTimeImmutable()) ``` to this ```php 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. ```php $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: https://github.com/doctrine/orm/blob/8c259ea5cb632dbb57001b2262048ae7fa52b102/lib/Doctrine/ORM/Query.php#L411-L413 *before trying to load metadata*. At this point, `$parameter` already have `datetime` type inside: ![datetime](https://user-images.githubusercontent.com/3725595/79611177-af593c00-8102-11ea-825b-a213dd0d19cf.png)

admin added the Won't Fix label 2026-01-22 15:33:28 +01:00

admin closed this issue

2026-01-22 15:33:29 +01:00

admin commented

2026-01-22 15:33:29 +01:00

@maks-rafalko commented on GitHub (Apr 18, 2020):

For maintainers: seems like this should be marked as won't fix according to Marco's answers in https://github.com/doctrine/orm/pull/8115

@maks-rafalko commented on GitHub (Apr 18, 2020): For maintainers: seems like this should be marked as `won't fix` according to Marco's answers in https://github.com/doctrine/orm/pull/8115

admin commented

2026-01-22 15:33:30 +01:00

@bastnic commented on GitHub (Apr 19, 2020):

FYI, the root issue happens in doctrine/persistence, see https://github.com/doctrine/persistence/issues/96

@bastnic commented on GitHub (Apr 19, 2020): FYI, the root issue happens in doctrine/persistence, see https://github.com/doctrine/persistence/issues/96

admin referenced this issue

2026-01-22 16:06:22 +01:00

[PR #6643] #6450 Add failing test in `QueryBuilderTest#testBetween()` #10085

admin referenced this issue

2026-01-22 16:07:16 +01:00

[PR #7184] Add BETWEEN() expr. #10356

Sign in to join this conversation.