Second Level Cache makes 350 requests to Redis for 20-rows result #6237

New Issue

admin · 2026-01-22T15:29:21+01:00

admin commented

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

Originally created by @yura3d on GitHub (May 14, 2019).

Q	A
Version	2.6.3

Support Question

Hello. I have an entity that has 16 ManyToOne associations to other entities. All the entities and associations are configured for using Second Level Cache.

I was very interested to see how efficiently Doctrine uses cache for reading, so I added logging to cache driver Doctrine\Common\Cache\RedisCache (to doFetch() and doFetchMultiple() methods).

I made a cacheable query for 20 rows with fetch-join associations (fetch mode is eager) and what I saw... These 20 rows of result produces for about 350 requests to Redis server. For the query root entities everything is fine: only 1 request is used for retrieve them through doFetchMultiple(). But for retrieving associations Doctrine makes 16 separate doFetch() requests for every association in every root entity (similar to Cartesian product). These requests happends even for equal association (there is a case when 20 root entities has equal entity in ManyToOne association, but Doctrine sends 20 separate GETs to Redis server for every root entity).

The log for the query above is here:

I don't understand why doFetchMultiple() is not using to retrieve associations. It could help to reduce the number of queries to cache from 350 to 4-5:

Result cache: Getting ids of root entities in query result - OK now
Second Level Cache: Getting all root entities by ids (1 call doFetchMultiple()) - OK now
Second Level Cache: Getting ids for every association in root entities (1 call doFetchMultiple()) - OK now
Second Level Cache: Getting entities for eager associations by ids for every root entity (1 call doFetchMultiple()) - ???

Originally created by @yura3d on GitHub (May 14, 2019). | Q | A |------------ | ----- | Version | 2.6.3 ### Support Question Hello. I have an entity that has 16 ManyToOne associations to other entities. All the entities and associations are configured for using Second Level Cache. I was very interested to see how efficiently Doctrine uses cache for reading, so I added logging to cache driver [**Doctrine\Common\Cache\RedisCache**](https://github.com/doctrine/cache/blob/master/lib/Doctrine/Common/Cache/RedisCache.php) (to [**doFetch()**](https://github.com/doctrine/cache/blob/master/lib/Doctrine/Common/Cache/RedisCache.php#L45) and [**doFetchMultiple()**](https://github.com/doctrine/cache/blob/master/lib/Doctrine/Common/Cache/RedisCache.php#L53) methods). I made a cacheable query for 20 rows with fetch-join associations (fetch mode is eager) and what I saw... These 20 rows of result produces for about 350 requests to Redis server. For the query root entities everything is fine: only 1 request is used for retrieve them through **doFetchMultiple()**. But for retrieving associations Doctrine makes 16 separate **doFetch()** requests for _every_ association in _every_ root entity (similar to Cartesian product). These requests happends _even_ for equal association (there is a case when 20 root entities has equal entity in ManyToOne association, but Doctrine sends 20 separate GETs to Redis server for every root entity). The log for the query above is here: ![image](https://user-images.githubusercontent.com/10958696/57693971-2d8ba380-7653-11e9-9fb9-f7c56a1bbf84.png) I don't understand why **doFetchMultiple()** is not using to retrieve associations. It could help to reduce the number of queries to cache from 350 to 4-5: 1. Result cache: Getting ids of root entities in query result - OK now 2. Second Level Cache: Getting all root entities by ids (1 call doFetchMultiple()) - OK now 3. Second Level Cache: Getting ids for every association in root entities (1 call doFetchMultiple()) - OK now 4. Second Level Cache: Getting entities for eager associations by ids for every root entity (1 call doFetchMultiple()) - ???

admin added the Bug Missing Tests labels 2026-01-22 15:29:21 +01:00

admin commented

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

@Ocramius commented on GitHub (May 14, 2019):

Would you be able to write a test case where this can be reproduced?

The test would:

set up a cache mock
run some queries
configure the cache mock to expect a certain number of fetch operations
retrieve the information

@Ocramius commented on GitHub (May 14, 2019): Would you be able to write a test case where this can be reproduced? The test would: 1. set up a cache mock 2. run some queries 3. configure the cache mock to expect a certain number of fetch operations 4. retrieve the information

admin commented

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

@lcobucci commented on GitHub (May 14, 2019):

@yura3d ~~it would also be nice to know if you are also using the L2C query cache for that particular use case,~~ perhaps you can send us the query (builder) you have too?

Edit: Nevermind, I double checked your text and it seems that you're using it

@lcobucci commented on GitHub (May 14, 2019): @yura3d ~it would also be nice to know if you are also using the L2C query cache for that particular use case,~ perhaps you can send us the query (builder) you have too? Edit: Nevermind, I double checked your text and it seems that you're using it

admin commented

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

@yura3d commented on GitHub (May 14, 2019):

@Ocramius, @lcobucci Thanks for your replies, I will publish test with my query and cache mock soon. And yes, I'm using L2C query cache (setCacheable(true) called on query object).

@yura3d commented on GitHub (May 14, 2019): @Ocramius, @lcobucci Thanks for your replies, I will publish test with my query and cache mock soon. And yes, I'm using L2C query cache (**setCacheable(true)** called on query object).

admin commented

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

@yura3d commented on GitHub (May 15, 2019):

The test is here, results are here
Should I create a PR with added test?

@yura3d commented on GitHub (May 15, 2019): The test is [here](https://github.com/yura3d/orm/blob/de4495b1a4a2d323597da170902eb5b7fb80903d/tests/Doctrine/Tests/ORM/Functional/Ticket/GH7708Test.php), results are [here](https://travis-ci.org/yura3d/orm/jobs/532935596) Should I create a PR with added test?

admin commented

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

@lcobucci commented on GitHub (May 15, 2019):

@yura3d that's awesome! Please send the PR, I'll check it ASAP

@lcobucci commented on GitHub (May 15, 2019): @yura3d that's awesome! Please send the PR, I'll check it ASAP

admin referenced this issue

2026-01-22 16:05:48 +01:00

[PR #6237] Allow arithmetic expression as DATE_ADD()/DATE_SUB() interval parameter #9908

Sign in to join this conversation.